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

Here is how to do it. Wish I had thought of it. This comes courtesy of Solomon Yakobson at
http://pipetalk.revealnet.com/~plsql

Option VARIABLE in SQL*Plus command EXIT syntax represents a user-defined or system variable (BUT NOT A BIND VARIABLE which is your case), such as SQL.SQLCODE. EXIT variable exits with the value of variable as the return code.
Oracle 8 SQL*Plus command EXIT syntax has additional option :BindVariable which represents a variable created in SQL*Plus with the VARIABLE command , and then referenced in PL/SQL, or other subprograms. :BindVariable exits the subprogram and returns you to SQL*Plus.

The only way to do it in Oracle 7 is to assign bind variable value to a substitution variable:

variable id number
column id new_value id noprint
begin
testa('h',:id);
end;
/

select :id id from dual;
exit id -- or exit &id

In Oracle8 it is easier:

variable id number
begin
testa('h',:id);
end;
/

exit :id

I have tested it and it works great. Just have to remember that on HP-UX the return code buffer is only a byte in size.

bob H. Received on Thu Jan 06 2000 - 02:46:04 CET