UNIX for Dummies Questions & Answers

i'm having real problems retrieving the returncode of my sqlplus-call. I found a lot of informations on the net, but havn't been able to get it running so far, so now i ask for some help

I do start the sqlplus out of my shell script with the parameters stored in the proc_clips.sql, which is perfectly working. But now i have created some real obviously mistakes like "trunate" or "comit" - but I still get the 0 as returncode. Does anybody have an idea what i'm doing wrong???

Thanks for your help, Martin

###### dbUpdate.sh ######

sqlplus -s userrob@db1/passwrob< proc_clips.sql
RETVAL=$?
echo $RETVAL
echo "SQLPUS-CLIPS"

###### proc_clips.sql ######

set echo on
whenever sqlerror exit 1
whenever oserror exit 2
exec martin_s4m_fictions;
exec MAJ_RANK;
trunate table T_DRAUT_TEMP_CLIPSFICTIONS;
comit;
exit 0

###### OUTPUT ######

SP2-0734: unknown command beginning "trunate ta..." - rest of line ignored.
SP2-0042: unknown command "comit" - rest of line ignored.
0
SQLPUS-CLIPS

What do your sqlplus scripts look like? Are you using the WHENEVER SQLERROR directive so that a code is sent to the O/S?

WHENEVER SQLERROR EXIT SQL.SQLCODE

Cheers,

Keith

Thank for the idea, but I already use

"whenever sqlerror exit 1" and
"whenever oserror exit 2"

so this can't eb the problem. Looks like sqlplus ignors my commands...

I have posted a part of my shell script, the sql-script i execute and the produced output on the bottom of my first script.

Is this a typo ( trunate ) ? Shouldn't this be truncate ?

Code:

trunate table T_DRAUT_TEMP_CLIPSFICTIONS;

It's because those are SQL*Plus errors and not SQL ,PL/SQL blocks (SQLERROR) or OS (OSERROR) errors.

For this reason, I suggest you exiting with a good return status different from 0, and test this value for successful completion of the sql script.
Every exit status is good, only dont' use 0

Here at work we have a whole bunch of sql programs made in this way for avoiding this problem.

He's using whenever sql/os error, so if
there is a SQL, PL/SQL or OS error, it's handled before the final exit:

Code:

$ sqlplus -s '/ as sysdba'<<<$(printf "whenever sqlerror exit 42\nselect 'hello' from x;\nexit 0")||echo "exit status $?"
select 'hello' from x
                    *
ERROR at line 1:
ORA-00942: table or view does not exist


exit status 42

It's about the syntax errors that are not handled by the whenever directive.