Re: Returning a value to UNIX shell
Date: 1995/11/01
Message-ID: <1995Nov1.231526.7118_at_nosc.mil>#1/1
si002_at_un.seqeb.gov.au ( SIMON INGLIS) wrote:
>Gordon Waddell (gwaddell_at_gssec.bt.co.uk) wrote:
>
>: I've got a thorny problem I need help with in the shortest period of time.
>: I have loads of PL/SQL in a db, I have a sql*plus script which executes it
>: and a UNIX shell script which calls this sql script.
>: I want to return a value from the PL/SQL so that it can be used as the exit
>: code (via the exit command) from sql*plus.
>: <snip>
>
>There is no way to return a value from PL/SQL via SQL*PLUS to the UNIX shell
>environment. The problem is with SQL*PLUS. SQL*PLUS's exit statement will
>only exit with a supplied value. There is no "IF" language construct in
>SQL*PLUS to enable you to exit with different error levels depending on a
>bind variable. Sorry no go.
>
> <snipped Simon's nice solution that assumes you use PRO*C.>
>
Our local Oracle guy came up with a cute solution that will work if
you want to return one of 2 values. (In my case I wanted a 0/1 to
use for no/yes.) The SQLPLUS calls a function that does the actual
table selects, if tests etc. If yes, return a value that is proper
for the function's datatype. (In my case, a date.) If no, return an
invalid value. (A value that is not a valid date.)
In the SQLPLUS that calls the function, code a WHEN SQLERROR EXIT 0
and in the regular exit code EXIT 1.
My UNIX script has to test $status. If $status = 1, I know that the
function returned a valid date.
Well _I_ liked it.......
(but it won't help Gordon return a numeric value.)
Louise Miller Received on Wed Nov 01 1995 - 00:00:00 CET