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

From: bob hunchberger <hunchberger_at_lessinc.com>
Date: Fri, 31 Dec 1999 11:45:57 -0500
Message-ID: <h25b4.231$g3.704_at_news1.iquest.net>

I think, I hope this is possible. I would like to pass either the function value or a procedure out parameter back to a variable in the SQLplus script that invoked the function or procedure. Then I want to use the exit command in SQLplus to make that value available to the OS, in this case the Korn shell on HP-UX v10. It is oracle v7.2 and v7.3.

Oh one more thing, the sqlscript runs on one machine and does a distributed invocation of the function or procedure on another machine.

I have tried the example in the SQLPlus Users Guide under the Variables command and I can not get it to work. It looks like this.

variable id number
begin
:id := rahpkg.testa ('hello',:id);
end;
print id

The procedure if invoked this way:

variable id number
execute testa ('hello',:id) <- id is an out parameter print id

works fine but I can't figure out how to get the id assigned to the exit command so that the shell can check it.
When I do an echo $? after the script executes I do not get the value of id?

If I try to invoke the procedure as part of a package

variable id number
execute rahpkg.testa ('hello',:id)

I get a PLS msg saying the 'rahpkg.testa' is not defined in this scope?? The package exists on another machine but I created a synonym rahpkg for the remote package.

create synonym rahpkg
for xxx.rah_at_dblink

the dblink works cause I can access tables on the remote machine using a synonym for the tables.

Basically, if someone could tell me how to pass parameter back to SQLplus from PL/SQL and then out to the OS that would be great.

Thanks

bob H
hunch_at_iquest.net Received on Fri Dec 31 1999 - 17:45:57 CET