How to call external program from SQL*Plus and catch its output into anonymous PL/SQL block

SQL> set define off
SQL> set echo off
SQL> set heading off
SQL> set feedback off
SQL> host rm ./mypipe.sql
SQL> host mkfifo mypipe.sql  
SQL> host echo -e "set heading off"'\n' "select 'It works' from dual;" | sqlplus -s "/ as sysdba" > mypipe.sql &
SQL> host ls -la mypipe.sql       
prw-r--r--. 1 oracle dba 0 Jul  8 14:25 mypipe.sql 
SQL> set serveroutput on
SQL> declare
file_contents VARCHAR2(32767):= '
@@mypipe.sql
';
begin
  dbms_output.put_line(file_contents);
end;
/ 

It works 

P.S.
It's a Linux example. AIX case: just remove -e option

Leave a Reply

Your email address will not be published. Required fields are marked *