Home » Infrastructure » Unix » Returning Values from Oracle to Unix Script
Returning Values from Oracle to Unix Script [message #98018] Tue, 11 November 2003 07:30 Go to next message
Mike Muldoon
Messages: 24
Registered: March 2003
Location: Edinburgh
Junior Member
Hi

Hope someone can help me here. I have a Unix script that calls a SQL file which runs an Oracle Stored Function, the command is below..

sqlplus $DATABASE @$tempSQL.sql $TO_REF_FILE $FILEDTM $PPPNNI $PPPPID

The tempSQL.sql files looks like the following:

set serveroutput on

DECLARE
errValue NUMBER;
BEGIN
errValue := IPGPPP.insertAuditTrailRec(1,0,'&&1','&&2','&&3','&&4',6,1639054);
END;
/
exit;

errVal is returned by the Oracle Function, 0 if it ran ok, a non zero error code if thee was a problem.

I wish to evaluate the value of errVal in the Unix script, if it is 0 then I am happy, otherwise the Oracle Function has failed and I don't want to proceed.

I am having trouble seeing how I can get the value held in the errVal variable back to the Unix script.

Thanks in advance if anyone can help out.

Cheers

Mike
Re: Returning Values from Oracle to Unix Script [message #98022 is a reply to message #98018] Thu, 13 November 2003 06:16 Go to previous messageGo to next message
Frank Naude
Messages: 4566
Registered: April 1998
Senior Member
Hi,

You can display the value of "errValue" on screen using -

DBMS_OUTPUT.PUT_LINE(errValue);

This can then be picked-up by the script for further processing. See http://www.orafaq.com/faqunix.htm#UNIXVAR for some examples.

Best regards.

Frank Naude
Re: Returning Values from Oracle to Unix Script [message #98025 is a reply to message #98022] Fri, 14 November 2003 08:47 Go to previous message
Mike Muldoon
Messages: 24
Registered: March 2003
Location: Edinburgh
Junior Member
That is great thanks. Using this I can pick it up from within the shell script using $?.

thanks for the help.
Previous Topic: svrmgrl error ld.so.1
Next Topic: Row Lock vs Table Lock in Oracle
Goto Forum:
  


Current Time: Sat Jan 16 12:52:43 CST 2021