Shell Script And SQLPLUS

maco_home · #1 (**permalink**) 08-24-2007

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

kduffin · #2 (**permalink**) 08-25-2007

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

maco_home · #3 (**permalink**) 08-25-2007

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.

matrixmadhan · #4 (**permalink**) 08-25-2007

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

Code:

trunate table T_DRAUT_TEMP_CLIPSFICTIONS;

radoulov · #5 (**permalink**) 08-25-2007

Quote:

Originally Posted by maco_home

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

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

robotronic · #6 (**permalink**) 08-25-2007

Quote:

Originally Posted by radoulov

It's because those are errors with SQL*Plus commands and not SQL commands,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.

radoulov · #7 (**permalink**) 08-25-2007

Quote:

Originally Posted by robotronic

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.

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Call sqlplus in the shell script	beaniebear	Shell Programming and Scripting	3	05-13-2008 09:30 AM
help me in sending parameters from sqlplus script to unix shell script	Hara	Shell Programming and Scripting	2	01-29-2008 03:31 PM
sqlplus and dbms_output.put_line in shell script	stuck1	Shell Programming and Scripting	6	12-20-2007 11:40 AM
running shell script from sqlplus	dkr123	Shell Programming and Scripting	9	07-20-2006 10:52 PM
passing parameters from a shell script to sqlplus	phani	Shell Programming and Scripting	2	03-13-2005 08:41 PM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread:


	Powered by