Re: Passing return codes from PL/SQL to SQLPlus script to OS

I believe the problem is that the variable exists during the life of the pl/sql block, but does not exist in the scope of SQL/Plus.

With some ingenuity, you might be able to have the function be a procedure block
which creates out text containing sql/plus code, which you execute dynamically. This would set a variable which you could execute prior to the EXIT. Really messy, but it might work. The problem really is the sql/plus is not very robust in this area, and does not approach the power of a full programming language...

...procedure...
creates text 'Define returncode='||'1' ... etc. saves in table tmp.

sqlplus....
begin
myfunc(x,y,z);
end;
/

select * from tmp
spool MySetReturn.sql
/

spool off
_at_MySetReturn
EXIT ReturnCode

(Procedure determines exit code,
prepares statement in sql/plus syntax,
and saves in table.
sql/plus spools output from table to
script file, then executes the script file).

Alternatively, the procedure could prepare the whole exit statement

   EXIT n;
then the _at_MySetReturn would cause the exit...

Good Luck,
Robert Proffitt

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 05 2000 - 19:11:33 CET