Re: Calling PL-SQL block from Shell Script

lal..._at_softhome.net wrote:
> Hi Guys,
> I am not able to get the return value of a PL-SQL block called from
> within the shell script. Here is my block of code in shell script:
>
> -----
> typeset v_result_1=`sqlplus -s system/manager <<TT
> set pagesize 0 heading off feedback off serveroutput on
> var v_result number;
> var v_lockname varchar2(200);
> BEGIN
>
> sys.dbms_lock.allocate_unique('alkuifmx',:v_lockhandle,86400000);
> :v_result:=sys.dbms_lock.request(:v_lockhandle,6,0);
> dbms_output.put_line(:v_result);
> END;
> /
> TT`
> echo "V_RESULT : " $v_result_1;
> ----
>
> Nothing is printed in the result.
> If I execute the same PL-SQL directly from SQLPLUS, I get the valid
> o/p ( as 1 or 0).
>
> SQL> set serveroutput on
> SQL> var v_result number;
> SQL> var v_lockname varchar2(200);
> SQL> BEGIN
> 2

sys.dbms_lock.allocate_unique('alkuifmx',:v_lockhandle,86400000);
> 3 :v_result:=sys.dbms_lock.request(:v_lockhandle,6,0);
> 4 dbms_output.put_line(:v_result);
> 5 END;
> 6 /
> 1
>
> PL/SQL procedure successfully completed.
>
> SQL>
> ------
>
> Please let me know how can I pass the o/p from PL-SQL block to my
shell
> script.

Are you trying to read stdout or get sqlplus exit code? How about this,

$ sqlplus system/manager<<EOF
  var num_ number;
  begin

     :num_ := 20;
  end;
  /
  exit :num_;
  EOF $ echo $?
20

Regards
/Rauf Received on Thu May 05 2005 - 12:14:21 CDT