Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: How to return an value from Oracle to Unix in a shell scrtip.

Re: How to return an value from Oracle to Unix in a shell scrtip.

From: <n-h_u-l_i-n__at_my-deja.com>
Date: Fri, 27 Oct 2000 02:30:54 GMT
Message-ID: <39f8e763.4006591@news.fuse.net>

On Mon, 23 Oct 2000 09:05:06 GMT, sybrandb_at_my-deja.com wrote:

OK, I've seen the other responses. None of them tell you how to catch an error when sqlplus exists.

Use any of the techniques you've seen described but add a:

WHENEVER SQLERROR ROLLBACK EXIT n
WHENEVER OSERROR ROLLBACK EXIT m

where n and m are some value. Actually SQL*Plus provides a built in,

SQL.ERRNO that you can use.

If you are using a shell here-doc don't put the username and password on the command line, put it in as the first line of the here-doc.

sqlplus -s << EOT
username/password
whenever sqlerror rollback exit 1
whenever oserror rollback exit 2
REM some sql here....
select user from dual;
exit
EOT case $? in

  1. echo "had a sqlplus error" ;;
  2. echo "had an o/s error" ;; esac

There are a dozen or more variations on this but this is how to do it.

...neil
>In article <39EDF96B.3A805BF1_at_email.com>,
> gojo <kckcabc_at_email.com> wrote:
>> Hi there,
>>
>> I've a shell script that runs some Oracle procedures, what I would
 like
>> to know is if the Oralce procedure was successful or had any error.
>> Basically a flag that tells me everything is OKed, so I can do
 something
>> else in the subsequent shell codes.
>>
>> Does anyone what are the methods to achieve that ?
>>
>> Thanks,
>> Ken.
>>
>>
>include
>whenever sqlerror exit return_value|variable_name|:bind_variable
>in your sqlplus script and the value you use will be passed as return
>code, and of course you can query that by $?
>
>
>
>Hth,
>--
>Sybrand Bakker, Oracle DBA
>
>All standard disclaimers apply
>------------------------------------------------------------------------
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Thu Oct 26 2000 - 21:30:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US