Re: Unix shell script capturing return value of stored procedure

kevinoleniczak_at_my-deja.com wrote:
>
> How do you write a shell script (ksh) to capture a return value from a
> stored function? If I have the following test fucntion...
>
> FUNCTION TESTME ( inName in VARCHAR2 ) RETURN NUMBER
> IS
> tmpVar NUMBER;
> BEGIN
> tmpVar := 321;
> RETURN tmpVar;
> END TESTME;
>
> And a script that looks like...
>
> sqlplus user/pwd <<PROCESS
> declare
> proc number;
> begin
> proc := TESTME ('$file');
> exit proc
> end;
> PROCESS
>
> echo $?
>
> I get the value 0 back which is incorrect.

#!/bin/ksh
Myvar=`sqlplus user/pwd <<-sqlEOF
set feedback off;
set page 0;
set serveroutput on;
whenever sqlerror exit failure;
Declare

    Proc Number;
Begin

    dbms_output.enable(1000000);
    Proc := Testme('$file');
    dbms_output.put_line(to_char(Proc)); End;
exit success;
sqlEOF
if [[ $? -ne 0 ]];then

    print -u2 "Fatal error from sqlplus: ${Myvae};     exit 1;
fi
print -u1 "The result is ${Myvar}";
exit 0;

.............................................................