Home » SQL & PL/SQL » SQL & PL/SQL » Oracle 9i - returning a procedure success/failure value to UNIX
Oracle 9i - returning a procedure success/failure value to UNIX [message #201728] Mon, 06 November 2006 09:19 Go to next message
palmeal
Messages: 7
Registered: August 2006
Junior Member
A bit of background first before I get to the main question. I have managed to execute a simple stored procedure from UNIX via a command file and UNIX script.

UNIX Script:
sqlplus uname/psswrd@sname @update_aap_delme.sql

SQL File - update_aap_delme.sql:
execute update_aap_delme(:finals);
exit;


Stored Procedure
create or replace procedure update_aap_delme
as
begin
update aap_delme set one_column = one_column + 1;
end update_aap_delme;
/


This works without any problems. The question I have is how can I return, say, a 0 for success and a 1 for failure to the calling UNIX Script ?

Adding RETURN_CODE=`echo $?` after the sqlplus line in the UNIX script will take the output value from the update_aap_delme.sql file but I'm not sure how to return a value from:
(1) the .sql file
(2) the stored procedure.

I have tried playing with exceptions and OUTPUT variables but haven't hit the correct combination so far. I am a relative newbie to Oracle (10+ years Sybase/SQL Server) so hopefully I am missing something simple.

I am not allowed to use dbms_job as this seems to be a corporate standard.

Any help at all would be appreciated.
Re: Oracle 9i - returning a procedure success/failure value to UNIX [message #201731 is a reply to message #201728] Mon, 06 November 2006 09:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here's the docs for the EXIT SLQ*Pluis command. It allows you to pass back the value of a bind variable, which is what I think you're looking for.
Re: Oracle 9i - returning a procedure success/failure value to UNIX [message #201739 is a reply to message #201731] Mon, 06 November 2006 09:50 Go to previous messageGo to next message
palmeal
Messages: 7
Registered: August 2006
Junior Member
Thanks for the quick answer.
I added a bind variable to the .sql file
variable finals number;
execute update_aap_delme(:finals);
exit :finals;


and an output variable to my stored procedure:
create or replace procedure update_aap_delme (final_status OUT number)
as
begin
update aap_delme set one_column = one_column + 1 where one_column = 1;
select 5 into final_status from dual;
end update_aap_delme;
/


and the UNIX code returned --> RETURN CODE IS --> 5

Thanks for the pointer !!
Re: Oracle 9i - returning a procedure success/failure value to UNIX [message #201744 is a reply to message #201739] Mon, 06 November 2006 10:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ooops - misread your post - I thought it was only getting the number out of SQL*Plus you were having trouble with. Sorry.

Glad you managed to get the answer anyway.
Re: Oracle 9i - returning a procedure success/failure value to UNIX [message #201771 is a reply to message #201744] Mon, 06 November 2006 12:21 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
you could elimitate moving parts by using a "here document"...

sqlplus uname/psswrd@sname << EOF > mylog.log
WHENEVER SQLERROR EXIT 99
variable finals number;
execute update_aap_delme(:finals);
exit :finals;
EOF

Previous Topic: Long and DbLink
Next Topic: Handling Java Exceptions from PL/SQL Code
Goto Forum:
  


Current Time: Fri Dec 09 19:31:44 CST 2016

Total time taken to generate the page: 0.06327 seconds