Re: Passing error from PL?SQL to UNIX script wrapper. (How?)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 20 Jun 1998 18:24:00 GMT
Message-ID: <3590fdfa.8565316@192.86.155.100>

A copy of this was sent to "ALEX J JENTILUCCI" <ALEXJENT_at_prodigy.net> (if that email address didn't require changing) On Sat, 20 Jun 1998 11:46:54 -0500, you wrote:

>I have been using RAISE_APPLICATION_ERROR(-20100, 'Some error message') for some time now with no apparent problem.
>
>Now I have a UNIX script which , among other things, executes a PL/SQL block via SQL*PLUS. It appears that when a NO_DATA_FOUND error occurs the UNIX script does not 'see' the error. In the UNIX script the first thing I do is check the exit code of the previous statement (sqlplus @filename etc..)
>
>When I echo out the exit code is 0.
>
>Am I missing something or have I been working too long.....
>
>How do some others deal which this???
>
>Thanks,
>Alex

Are you using 'whenever sqlerror exit sql.sqlcode'??

For example, on my solaris machine the following csh script:

#!/bin/csh -f

cat > test.sql <<"EOF"
whenever sqlerror exit sql.sqlcode
declare

    n number;
begin

    select * into n from dual where 1 = 0; end;
/
exit
"EOF" sqlplus tkyte/tkyte @test.sql

echo The exit code is: $status

---

prints out:

$ test.sh

SQL*Plus: Release 8.0.3.0.0 - Production on Sat Jun 20 14:22:43 1998 (c) Copyright 1997 Oracle Corporation. All rights reserved. Connected to:
Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production With the Partitioning and Objects options PL/SQL Release 8.0.3.0.0 - Production

declare
*
ERROR at line 1:
ORA-01403: no data found

Disconnected from Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production With the Partitioning and Objects options PL/SQL Release 8.0.3.0.0 - Production
The exit code is: 123

So, it sees 123 as the exit status...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Jun 20 1998 - 13:24:00 CDT