How to know in a unix script if oracle procedure executed or failed?

[vishal]

Hi,

I am writing my first script to execute a Oracle procedure through a
unix script. Could you tell me how do I do error handling here? How do
I know in my script if the procedure executed successfully or failed?
Thanks

#!/usr/bin/ksh
sqlplus user/password@name <<EOC
set time on
set echo on
select sysdate from dual
;
spool procedureName.log
exec procedureName
spool off
exit
EOC

[Sybrand Bakker]

On 10 Dec 2003 13:18:38 -0800, c_******@hotmail.com (vishal) wrote:

Hi,

I am writing my first script to execute a Oracle procedure through a
unix script. Could you tell me how do I do error handling here? How do
I know in my script if the procedure executed successfully or failed?
Thanks

#!/usr/bin/ksh
sqlplus user/password@name <<EOC
set time on
set echo on
select sysdate from dual
;
spool procedureName.log
exec procedureName
spool off
exit
EOC

In the sql script include
whenever sqlerror exit failure
and the script will exit with a non-zero status.
If this is not sufficient look up whenever sqlerror and whenever
oserror on http://tahiti.oracle.com
--
Sybrand Bakker, Senior Oracle DBA

[Thorsten Häs]

Hello,

you can use some other solution:

#!/usr/bin/ksh

sqlplus user/password@name <<EOC>/tmp/log/log_file_unix_shell.log
set time on
set echo on
select sysdate from dual
;
spool procedureName.log
exec procedureName
spool off
exit
EOC

you find the output of your user(SQL)-session in the
/tmp/log/log_file_unix_shell.log-File.

best regards
thorsten häs

On Wed, 10 Dec 2003 23:49:22 +0100, Sybrand Bakker
<go********@sybrandb.demon.nl> wrote:
On 10 Dec 2003 13:18:38 -0800, c_******@hotmail.com (vishal) wrote:

Hi,

I am writing my first script to execute a Oracle procedure through a
unix script. Could you tell me how do I do error handling here? How do
I know in my script if the procedure executed successfully or failed?
Thanks

#!/usr/bin/ksh
sqlplus user/password@name <<EOC
set time on
set echo on
select sysdate from dual
;
spool procedureName.log
exec procedureName
spool off
exit
EOC

In the sql script include
whenever sqlerror exit failure
and the script will exit with a non-zero status.
If this is not sufficient look up whenever sqlerror and whenever
oserror on http://tahiti.oracle.com
--
Sybrand Bakker, Senior Oracle DBA

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

[Sybrand Bakker]

On Thu, 11 Dec 2003 19:28:33 +0100, Thorsten Häs
<ei*********@t-online.de> wrote:

Hello,

you can use some other solution:

#!/usr/bin/ksh

sqlplus user/password@name <<EOC>/tmp/log/log_file_unix_shell.log
set time on
set echo on
select sysdate from dual
;
spool procedureName.log
exec procedureName
spool off
exit
EOC

you find the output of your user(SQL)-session in the
/tmp/log/log_file_unix_shell.log-File.

best regards
thorsten häs

He wants to know *in the script* whether his procedure succeeded., not
by looking at log files. Your solution doesn't work, so why contradict
me with a non-working solution where my solution does work?
--
Sybrand Bakker, Senior Oracle DBA

[Billy Verreynne]

Sybrand Bakker <go********@sybrandb.demon.nl> wrote

He wants to know *in the script* whether his procedure succeeded., not
by looking at log files. Your solution doesn't work, so why contradict
me with a non-working solution where my solution does work?

Exactly.

In addition you can also declare a bind variable in SQL*Plus and set
it in anon PL/SQL blocks in the script (kind of like a process status
code). Then terminate the script with the EXIT command using the bind
variable as parameter.

Also, the OP should note that the exitcode in Unix is a byte, while
the actual exitcode in SQL*Plus (especially when using SQL%SQLCODE to
return ORA error numbers) is two bytes. Thus you cannot determine the
actual Oracle error code in the Unix script by using the exitcode (in
that case you will need to grep the spoolfile).

--
Billy

[Ubiquitous]

vishal <c_******@hotmail.com> wrote:

: I am writing my first script to execute a Oracle procedure through a
: unix script. Could you tell me how do I do error handling here? How do
: I know in my script if the procedure executed successfully or failed?
: Thanks

What I do is run the script with the output piped to a log file and within
the script itself, test for errors...
Within the stored procedure itself, there's a bunch of EXCEPTION handling
clauses with more specific error messages.

$HOME/bin/qs "execute load_table('X')"
echo "load_table has executed"

if [ $? -ne 0 ]
then
echo "An error has occurred"
exit
fi

err=`grep 'ORA-' $HOME/logfile|wc -1
if [ $err -ne 0 ]
then
echo "An ORACLE error has occurred"
exit
fi

Hope this helps!