I have a unix script that calls a procedure in Oracle database. I need to do some other things in the script based on the successfull completion of the stored procedure. How do I know that the procedure has successfully completed before proceeding to my next task in the script?
Thanks in advance for the help.
Since nobody responded to my question so far I assume that it isn't that easy to achieve this. What I am thinking is I could send the exceptions/errors to a file and then grep the file for any ORA errors. The thing is I have some exit conditions from the procedure other than the ORA errors. So is it possible to assign my own ORA error numbers for the user defined exceptions? Thanks
I don't write Unix scripts much, but presumably the script doesn't actually call the procedure, rather it invokes a sqlplus session that calls the procedure? You can use the "EXIT <n>" and "WHENEVER SQLERROR EXIT <n>" sqlplus commands to return status info.
To be more specific I am calling the procedure from Unix script by invoking sqlplus.
I don't think WHENEVER SQLERROR works for me because I need to exit the procedure if some conditions are not met prior to processing any SQL statement. I can list you the portion of the script that calls the procedure so that you will get a better understanding.
sqlplus -s test_721_d/tecsys@fnok_dms >$LOGFILE <<-EOF
set feed off veri off head off
set serveroutput on
set serveroutput off
set feed on veri on head on
I need to continue with the script only if the sqlplus session is succes, no SQL errors, no exceptions, met all user defined conditions. Thanks.
That's true for exceptions. But what about user defined exit conditions where I don't raise an exception. eg. I don't want to continue the procedure if a particular table is empty. I just use RETURN to terminate the procedure, instead should I raise an exception that eventually the WHENEVER SQLERROR will catch?
I thought it was working, but I am wrong. My unix script is not able to get the exit status from sqlplus, even though I could exit from sqlplus using WHENEVER SQLERROR EXIT. How do I pass the exit value(ie sql.sqlcode) to the unix script?
Any way I found a work aroud by scanning my log file for ERROR lines and stop the process if any found.