Home » SQL & PL/SQL » Client Tools » How to call external program from SQL*Plus and catch its output into anonymous PL/SQL block (11g and higher, Unix like)
How to call external program from SQL*Plus and catch its output into anonymous PL/SQL block [message #678902] Tue, 21 January 2020 11:45 Go to next message
Olexandr Siroklyn
Messages: 27
Registered: September 2018
Location: USA
Junior Member
sqlplus "/ as sysdba"
...
SQL>host rm ./mypipe.sql
SQL>host mkfifo mypipe.sql
SQL>host echo "set heading off"'\n' "select 'It works' from dual;" | sqlplus -s "/ as sysdba" > mypipe.sql &
SQL>set serveroutput on
SQL>declare
file_contents VARCHAR2(32767):='
@@mypipe.sql
';
begin dbms_output.put_line(file_contents); end;
/

It works

SQL>


P.S.
It's AIX example. For Linux use -e option for echo command
P.P.S
SP2-0317: expected symbol name is missing message can be ignored.
Re: How to call external program from SQL*Plus and catch its output into anonymous PL/SQL block [message #678904 is a reply to message #678902] Tue, 21 January 2020 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
SP2-0317: expected symbol name is missing message can be ignored.
At which line?
Add "set echo on" and copy and paste the whole SQL*Plus session.
Before, Please read How to use [code] tags and make your code easier to read.

It works with a normal file (I'm on windows so can't test named pipe):
SQL> host dir a*.* >c:\t.lst

SQL> declare v varchar2(32500);
  2  begin
  3    v:= q'[
  4  @c:\t.lst
 14  ]';
 15  dbms_output.put_line(v);
 16  end;
 17  /

 Le volume dans le lecteur E s'appelle Data
 Le numéro de série du volume est 4A9F-7C49

 Répertoire de E:\Temp\Listing


23/09/2019  07:08               708 aud_opt.LST
26/07/2019  06:35               522 awrSeg2.LST
26/07/2019  06:38
            2 awrSegStat.LST
               3 fichier(s)            1 232 octets
               0 Rép(s)   6 415 843 328
 octets libres


PL/SQL procedure successfully completed.
Re: How to call external program from SQL*Plus and catch its output into anonymous PL/SQL block [message #678905 is a reply to message #678904] Tue, 21 January 2020 13:01 Go to previous messageGo to next message
Olexandr Siroklyn
Messages: 27
Registered: September 2018
Location: USA
Junior Member
SQL> set echo on
SQL> host rm ./mypipe.sql

SQL> host mkfifo mypipe.sql

SQL> host echo "set heading off"'\n' "select 'It works' from dual;" | sqlplus -s "/ as sysdba" > mypipe.sql &
SP2-0317: expected symbol name is missing

SQL> set serveroutput on
SQL> declare
  2     file_contents VARCHAR2(32767):='
  3  @@mypipe.sql
  3  ';
begin
        dbms_output.put_line(file_contents);
end;
/
  4  It works
  5
  6    7    8    9   10


It works



PL/SQL procedure successfully completed.

SQL> 
Re: How to call external program from SQL*Plus and catch its output into anonymous PL/SQL block [message #678906 is a reply to message #678905] Tue, 21 January 2020 13:05 Go to previous messageGo to next message
Olexandr Siroklyn
Messages: 27
Registered: September 2018
Location: USA
Junior Member
>>It works with a normal file

My point is not about to display normal file's content. It's about to run whatever and get run-time output back.
Re: How to call external program from SQL*Plus and catch its output into anonymous PL/SQL block [message #678908 is a reply to message #678906] Tue, 21 January 2020 13:43 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think I see the problem.
Insert "set define off" at the beginning.
Reason is:
SQL> prompt &
SP2-0317: expected symbol name is missing
&
SQL> set define off
SQL> prompt &
&
Re: How to call external program from SQL*Plus and catch its output into anonymous PL/SQL block [message #678914 is a reply to message #678906] Wed, 22 January 2020 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
My point is [...] about to run whatever and get run-time output back.
So you have it in the file and then do whatever you want with its content.

Re: How to call external program from SQL*Plus and catch its output into anonymous PL/SQL block [message #678921 is a reply to message #678914] Wed, 22 January 2020 07:48 Go to previous messageGo to next message
Olexandr Siroklyn
Messages: 27
Registered: September 2018
Location: USA
Junior Member
Michel Cadot wrote on Wed, 22 January 2020 02:54

Quote:
My point is [...] about to run whatever and get run-time output back.
So you have it in the file and then do whatever you want with its content.

In provided example named pipe file contains no SQL*Plus output when file is filled out, by the other words there no any database established connections, i.e. SQL*Plus execution, until try to read named pipe file content.
Re: How to call external program from SQL*Plus and catch its output into anonymous PL/SQL block [message #678928 is a reply to message #678921] Wed, 22 January 2020 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your example, I don't understand the point but maybe the example is not representative of what you want/need to do.
Anyway, does this work now as expected?

Re: How to call external program from SQL*Plus and catch its output into anonymous PL/SQL block [message #678929 is a reply to message #678928] Wed, 22 January 2020 10:29 Go to previous messageGo to next message
Olexandr Siroklyn
Messages: 27
Registered: September 2018
Location: USA
Junior Member
Michel Cadot wrote on Wed, 22 January 2020 11:22

From your example, I don't understand the point but maybe the example is not representative of what you want/need to do.
Anyway, does this work now as expected?

I've just shared my experience.
Re: How to call external program from SQL*Plus and catch its output into anonymous PL/SQL block [message #678930 is a reply to message #678929] Wed, 22 January 2020 10:41 Go to previous message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ah, OK! thanks for this, it will help.
I'll move the topic to "Client Tools" forum.

Previous Topic: Help with tnsname.ora
Next Topic: help with installation of Oracle client
Goto Forum:
  


Current Time: Sun Sep 27 16:30:33 CDT 2020