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.