Oracle 9i - Return Code from Procedure to UNIX
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.
sqlplus uname/psswrd@sname @update_aap_delme.sql
SQL File - update_aap_delme.sql:
create or replace procedure update_aap_delme
update aap_delme set one_column = one_column + 1;
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.
Try this in .sql file:
WHENEVER SQLERROR EXIT 1;
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Thanks for the reply - I have this working now.
is the same thing as
Click Here to Expand Forum to Full Width