Re: Returning a value to UNIX shell

In article <47fbc2$imb_at_inet-nntp-gw-1.us.oracle.com> rmanalac_at_oracle.com writes:
>Louise Miller <miller_at_louise.ucsd.edu> writes:
>|> 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
>|>
>|>
>Here's another solution or at least the beginning of one:
>=========
>SQL> column foo new_value _foo;
>SQL> select 5 foo from dual;
>
> FOO
>----------
> 5
>
>SQL> exit _foo;
>Disconnected from Oracle7 Server Release 7.1.6.2.0 - Production Release
>With the distributed, replication and parallel query options
>PL/SQL Release 2.1.6.2.0 - Production
>enrique% echo $status
>5
>==========
>Extended further
>SQL> variable x number;
>SQL> column foo new_value _foo
>SQL> begin
> 2 :x := 5;
> 3 end;
> 4 /
>
>PL/SQL procedure successfully completed.
>
>SQL> select :x foo from dual;
>
> FOO
>----------
> 5
>
>SQL> exit _foo;
>Disconnected from Oracle7 Server Release 7.1.6.2.0 - Production Release
>With the distributed, replication and parallel query options
>PL/SQL Release 2.1.6.2.0 - Production
>enrique% echo $status
>5
>
>===========
>Remember that the return status is usually limited by Unix to an
>8-bit number (I forget if it's signed or unsigned).
>
>Hope this helps
>Roderick

SQL> column foo new_value _foo;
SQL> select 5 foo from dual;

       FOO
       ----------
		5

SQL> exit _foo;
Disconnected from ORACLE7 Server Release 7.0.16.4.0 - Production With the procedural and distributed options PL/SQL Release 2.0.18.1.0 - Production
You_at_wish:/usr/users/dontyou $ echo $status sh: status: parameter not set

(Now I try the csh:)

wish:/usr/users/dontyou
12 % sqlplus You/thinkIwouldtell

SQL*Plus: Release 3.1.3.2.1 - Production on Tue Nov 7 09:45:08 1995

Copyright (c) Oracle Corporation 1979, 1992. All rights reserved.

Connected to:
ORACLE7 Server Release 7.0.16.4.0 - Production With the procedural and distributed options PL/SQL Release 2.0.18.1.0 - Production

SQL> column foo new_value _foo;
SQL> select 5 foo from dual;

       FOO
       ----------
		5

SQL> exit _foo;
Disconnected from ORACLE7 Server Release 7.0.16.4.0 - Production With the procedural and distributed options PL/SQL Release 2.0.18.1.0 - Production

wish:/usr/users/dontyou
13 % echo $status
5

Seems to only work in csh... not something I would recommend for production scripts.

-- 
Joel Garry               joelga_at_rossinc.com               Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.   <> <>
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.            \ V /
panic: ifree: freeing free inodes...                                   O


-- 
Joel Garry               joelga_at_rossinc.com               Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.   <> <>
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.            \ V /
panic: ifree: freeing free inodes...                                   O