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

[bob hunchberger]

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@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
hu...@iquest.net

[James Lorenzen]

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....@news1.iquest.net>,

Sent via Deja.com http://www.deja.com/
Before you buy.

[Bob Hunchberger]

this is what happens when I did as you suggested. this is on HP-UX v 10.
I get the same message if I make it a function as well???

bob H.

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

Oracle7 Server Release 7.3.4.2.0 - Production
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.4.2.0 - Production

PL/SQL procedure successfully completed.

Usage: { EXIT | QUIT }
[ SUCCESS | FAILURE | WARNING | n | Variable ] [ COMMIT | ROLLBACK ]
Disconnected from Oracle7 Server Release 7.3.4.2.0 - Production

[rtpro...@my-deja.com]

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
@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 @MySetReturn would cause the exit...

Good Luck,
Robert Proffitt

[bob hunchberger]

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.

[vin...@my-deja.com]

You can use the following syntax in your Shell Script :
BATCH_NUMBER=`sqlplus -s scott/tiger <<EOF
set heading off
set pagesize 0
set termout on
set wrap on
set feedback off
set echo off
set serveroutput on
execute rahpkg.testa ('hello');
exit
EOF`

And in your PL/SQL code at the end of rahpkg.testa, add the following
statement
-- Display id
dbms_output.put_line(id);

Hope it will help

Regards

vin...@hotmail.com

[shubhob...@gmail.com]

Hi,

I have a similar problem and a similar approach for the solution of the same.
my code traps various exit codes for various conditions and stores the value to a local variable; i'm then passing it to a bind var which i am using to store the exit code.
My code is some thing like,,,

variable errflag number
begin
variable vflag number;
if
cond 1
then
vflag:=1;
.
.
.
.
.
elsif
cond n
then
vflag:=n;
else
...do nothing..
end if;
:errflag:=vflag;
end;
/
exit :errflag





but i am getting the below error after running

SP2-0670: Internal number conversion failed

Usage: { EXIT | QUIT } [ SUCCESS | FAILURE | WARNING | n |

<variable> | :<bindvariable> ] [ COMMIT | ROLLBACK ]


any ideas??? :\