Re: Returning a value to UNIX shell

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.
 

: I almost have it using temporary tables and bind variables but I cannot get
: exit to take a bind variable name.
 

: I could also be on completely the wrong track.
 

: If anyone therefore, knows how to get a numerical value from a PL/SQL
: procedure to the UNIX shell level please tell.

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. The way I got around this problem was to write a PRO*C program that simply called the stored procedure and returned the value to the UNIX environemtn via the C "exit" function call. Not very elegant, but it's the only way.

Simon Inglis
si002_at_seqeb.gov.au Received on Wed Nov 01 1995 - 00:00:00 CET