OraFAQ Forum: Client Tools » How to call external program from SQL*Plus and catch its output into anonymous PL/SQL block

Home » Other » Client Tools » How to call external program from SQL*Plus and catch its output into anonymous PL/SQL block (11g and higher, Unix like)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Olexandr Siroklyn
Messages: 36
Registered: September 2018
Location: USA

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.

Report message to a moderator

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

Michel Cadot
Messages: 68512
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.

Report message to a moderator

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

Olexandr Siroklyn
Messages: 36
Registered: September 2018
Location: USA

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>

Report message to a moderator

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

Olexandr Siroklyn
Messages: 36
Registered: September 2018
Location: USA

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.

Report message to a moderator

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

Michel Cadot
Messages: 68512
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 &
&

Report message to a moderator

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

Michel Cadot
Messages: 68512
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.

Report message to a moderator

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

Olexandr Siroklyn
Messages: 36
Registered: September 2018
Location: USA

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.

Report message to a moderator

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

Michel Cadot
Messages: 68512
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?

Report message to a moderator

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

Olexandr Siroklyn
Messages: 36
Registered: September 2018
Location: USA

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.

Report message to a moderator

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

Michel Cadot
Messages: 68512
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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Oct 04 19:30:01 CDT 2023