Home » SQL & PL/SQL » SQL & PL/SQL » Writing output from variables in pl/sql to unix shell script
Writing output from variables in pl/sql to unix shell script [message #254866] Sun, 29 July 2007 16:01 Go to next message
bmathew
Messages: 1
Registered: July 2007
Junior Member
Hi, I'm executing a PL/SQL procedure from a unix shell script and would like to know how I can pass back values from variables in the PL/SQL procedure to the shell script. I want to capture the error messages from the PL/SQL procedure and pass them back to the shell script, which I will then write to an error file. I can't use Oracle's file writer utility in this case because the shell script that calls the procedure is not the same server that Oracle sits on, and I need to write the error messages to a log file on this server. Thanks!

[Updated on: Mon, 30 July 2007 01:16] by Moderator

Report message to a moderator

Re: Writing output from variables in pl/sql to unix shell script [message #254867 is a reply to message #254866] Sun, 29 July 2007 16:07 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
One possible way would be to use UTL_TCP to interface to "standard" *nix remote logging capabiity.
Re: Writing output from variables in pl/sql to unix shell script [message #254912 is a reply to message #254866] Mon, 30 July 2007 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Otherwise there is UTL_FILE package to write into a file.

Regards
Michel
Re: Writing output from variables in pl/sql to unix shell script [message #254966 is a reply to message #254866] Mon, 30 July 2007 03:41 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

If you can't use UTL_FILE, then why don't you make a SQL file which calls the procedure and spools the output or error messages in the spool file and then copy the contents to the log file...

Regards
Himanshu
Re: Writing output from variables in pl/sql to unix shell script [message #255108 is a reply to message #254966] Mon, 30 July 2007 11:40 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
#!/bin/ksh
sqlplus -s scott/tiger@dev <<EOF > tmp.txt
set pagesize 0 feedback off verify off heading off echo off
set serveroutput on
--select name from v\$parameter where name like 'nls%' and rownum <6;
select table_name from user_tables where rownum <6;
begin
  dbms_output.put_line('output parm#1');
  dbms_output.put_line('output parm#2');
end;
/
exit;
EOF

while read reslt_line
do
  echo "==>"$reslt_line
done < tmp.txt

==>BONUS
==>CUSTOMER
==>DATE_TAB
==>DEPT
==>DUMMY
==>output parm#1
==>output parm#2
Previous Topic: Passing list of values to Stored Procedure
Next Topic: How to manipulate data at bit level?
Goto Forum:
  


Current Time: Sun Dec 04 08:33:13 CST 2016

Total time taken to generate the page: 0.12235 seconds