OraFAQ Forum: Unix » Returning Values from Oracle to Unix Script

Home » Infrastructure » Unix » Returning Values from Oracle to Unix Script

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

Returning Values from Oracle to Unix Script [message #98018]

Tue, 11 November 2003 07:30

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

Report message to a moderator

Re: Returning Values from Oracle to Unix Script [message #98022 is a reply to message #98018]

Thu, 13 November 2003 06:16

Frank Naude
Messages: 4581
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

Report message to a moderator

Re: Returning Values from Oracle to Unix Script [message #98025 is a reply to message #98022]

Fri, 14 November 2003 08:47

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.

Report message to a moderator

Previous Topic:	svrmgrl error ld.so.1
Next Topic:	Row Lock vs Table Lock in Oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 04:47:18 CST 2024