topic Re: Shell Script in Operating System - HP-UX

Shell Script

Prabhu_7 — Mon, 19 May 2003 19:29:17 GMT

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

Re: Shell Script

Shannon Petry — Mon, 19 May 2003 19:34:03 GMT

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

Re: Shell Script

A. Clay Stephenson — Mon, 19 May 2003 19:44:27 GMT

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.

Re: Shell Script

Prabhu_7 — Mon, 19 May 2003 19:45:21 GMT

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

Re: Shell Script

Brian Crabtree — Mon, 19 May 2003 23:16:05 GMT

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

Brian

Re: Shell Script

Yogeeraj_1 — Tue, 20 May 2003 07:16:00 GMT

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

Re: Shell Script

Massimo Bianchi — Tue, 20 May 2003 08:42:10 GMT

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