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

The "EXIT" SQL statement will return the variable. If you take your variable from this statement:

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

And replace the "PRINT" with "EXIT" it should work fine. I.E.:

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

This following statement assumes that "rahpkg.testa" is a function that is returning a value. It may work if it is changed to remove the ":id" assignment. (and "id" should not be a bind variable in this context.

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

You also need to apply the synonym to the function / procedure level.

HTH
  James

In article <h25b4.231$g3.704_at_news1.iquest.net>,   "bob hunchberger" <hunchberger_at_lessinc.com> wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Jan 03 2000 - 17:07:17 CET