cancel
Showing results for 
Search instead for 
Did you mean: 

Shell Script

Prabhu_7
Frequent Advisor

Shell Script

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


6 REPLIES
Shannon Petry
Honored Contributor

Re: Shell Script

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
Microsoft. When do you want a virus today?
A. Clay Stephenson
Acclaimed Contributor

Re: Shell Script

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.


If it ain't broke, I can fix that.
Prabhu_7
Frequent Advisor

Re: Shell Script

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
Honored Contributor

Re: Shell Script

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

Brian
Yogeeraj_1
Honored Contributor

Re: Shell Script

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

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Massimo Bianchi
Honored Contributor

Re: Shell Script

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