Calling PL-SQL block from Shell Script

From: <lalitb_at_softhome.net>
Date: 4 May 2005 22:57:44 -0700
Message-ID: <1115272664.120370.98600@g14g2000cwa.googlegroups.com>

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. Received on Thu May 05 2005 - 00:57:44 CDT