Re: Unix shell script capturing return value of stored procedure

I didn't see a closing backtick there Walter. It should be after final sqlEOF I think.

"Walter T Rejuney" <BlueSax_at_Unforgetable.com> wrote in message news:3A5A15DF.D45A688F_at_Unforgetable.com...
> 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`

 ^^^ added a closing backtick on above line.
> if [[ $? -ne 0 ]];then
> print -u2 "Fatal error from sqlplus: ${Myvae};
> exit 1;
> fi
> print -u1 "The result is ${Myvar}";
> exit 0;
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> .............................................................
Received on Tue Jan 09 2001 - 04:33:39 CST