Shell Script

[Prabhu_7]

I have a shell script from which i'm calling a
Oracle script

.
.
Sqlplus <..> @mysql.sql

How can i check whther this SQL statement has been executed successfully ?
Inside my .sql file i have given "Whenever SQL error
Rollback and EXIT".
How can i know whether the SQL statement was successfull or rollbacked due to some SQL error?

if [ $? = 0 ]
Will this work for SQL Statements ?


Help !!!

Raj

[Shannon Petry]

The easiest way is to use the "test" function.

test 'your commands'.

I.E.

if [ test 'mystuff.sh' ] ; then
echo "It worked"
else
echo "it failed ;("
fi


you can also use the return value from the command.
ls /tmp
RETVAL=$?
if the return value is 0, then the command succeeded, if non 0, it failed..

Regards,
Shannon

[A. Clay Stephenson]

All that $? will tell you is the status of the sqlplus command itself. Unless sqlplus was unable to start (e.g. bad patch), all you will ever see is zero.

You really have to do much more to get meaningful error codes. The typical approach is to add a spool filexxx command and then use grep/awk/perl to examine the spool file after sqlplus has completed. Moreover, because the same command might be run more than once at the same time, the better method is to have your shell script build your SQL script "on the fly" and create PID dependent temporary spool file names to avoid pathname collisions.

[Prabhu_7]

I wanted for SQL Statement

I have a shell script from which i'm calling a
Oracle script

.
.
Sqlplus <..> @mysql.sql

How can i check whther this SQL statement has been executed successfully ?
Inside my .sql file i have given "Whenever SQL error
Rollback and EXIT".
How can i know whether the SQL statement was successfull or rollbacked due to some SQL error?

if [ $? = 0 ]


This works fine for Unix commands , but Will this work for SQL Statements also ?


Help !!!

Raj

[Brian Crabtree]

Using an "exit 1" from sqlplus will change the status of $? to 1.

Brian

[Yogeeraj_1]

hi,

Another option that might work for you would be to redirect sqlplus's output.

Like:

#!/bin/ksh

sqlplus -s scott/tiger <> test.lst
@test
exit
STOP


then do a grep looking for errors!

grep -i "ora" test.lst |mailx

or analyze the file using any method you want.

Hope this helps!

regards
Yogeeraj

[Massimo Bianchi]

Hello,
another hint is to change the sql,
from

whenever sql error rollback and exit ;

to whenever sql error goto do_something;

and the procedure

do_something:
- signal the error
- rollback
- exit

HTH, Massimo