Re: SQL*PLUS and shell scripts

Another thing you could do, but is a bit long winded, is in the SQL write the error to a DBMS pipe, then exit from SQL with a non 0 value. In the shell script detect the error, then call an error routine to read the DBMS pipe, then using the SQL*Plus print the error number and capture that in UNIX i.e.

• Your SQL error_handler: dbms_pipe.pack_message(SQLCODE); dbms_pipe.send_message(<pipe name>); exit TRUE;
• Your Shell run_sql_script_here

if [ $? ]; then

     sqlcode=`sqlplus user/pass_at_instance @error_proc` fi

• error_proc.sql variable ret number execute begin dbms_pipe.receive_message(<pipe name>) :ret := dbms_pipe.unpack_message end print ret

Or something like that. I think the syntax needs little word, but the idea is there.

Regards
Kev.D

John Strange <jstrange_at_imtn.dsccc.com> wrote in article <5gbihh$di5_at_camelot.dsccc.com>...
> You could try spooling to an error file in your WHENEVER SQLERROR,
> EXIT 1, and let the script read the error file if the status is a 1.
>
>
> Rob Spies (rspies_at_minn.net) wrote:
> : Prashant Gaba <pgaba_at_usccmail.bms.com> wrote:
 

> : >Hi All,
 

> : >I am trying to productionise some of the queries that we run.
> : >My question is:
> : > How can I return the sql error code to a Unix shell script?
> : >I tried putting "WHENEVER SQLERROR EXIT SQLCODE" and although sqlplus
> : >exits with an error it does not return the correct code to the shell
> : >script which always displays 1.
 

> : >I also tried hardcoding 942 in place of sqlcode and when i displayed
 the
> : >error code in the shell script it displays 174.
> : >I know this is possible as I have done this DCL on VMS but I am
 missing
> : >something here.
> : >Can some one help????
 

> : >Thanks
> : >--
> : >Prashant Gaba
> : >Consultant
> : >e-mail: pgaba_at_usccmail.bms.com
 

> : Hi...
 

> : I don't know if this will help much, but it might get you going in the
> : right direction...
 

> : First, the UNIX error code has range of 0-255. So, if you calculate
> : the modulus 256 of 942, you get 174. When you hardcoded 942 as your
> : exit code, UNIX converted 942 to 174. So that seems to be working
> : OK... but probably not what you were looking for.
 

> : I have also tried passing sql error codes back to the shell script,
> : but to no avail. I'd be interested in finding out a way to do this,
> : too...
 

> : Rob
> : rspies_at_minn.net
>
>
> --
> This posting represents the personal opinions of the author. It is not
 the
> official opinion or policy of the author's employer. Warranty expired
 when you
> opened this article and I will not be responsible for its contents or
 use.
>
Received on Fri Mar 14 1997 - 00:00:00 CST