Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!z14g2000cwz.googlegroups.com!not-for-mail
From: "Rauf Sarwar" <rs_arwar@hotmail.com>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Calling PL-SQL block from Shell Script
Date: 5 May 2005 10:14:21 -0700
Organization: http://groups.google.com
Lines: 64
Message-ID: <1115313261.489563.207990@z14g2000cwz.googlegroups.com>
References: <1115272664.120370.98600@g14g2000cwa.googlegroups.com>
NNTP-Posting-Host: 195.92.67.75
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1115313265 19393 127.0.0.1 (5 May 2005 17:14:25 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 5 May 2005 17:14:25 +0000 (UTC)
In-Reply-To: <1115272664.120370.98600@g14g2000cwa.googlegroups.com>
User-Agent: G2/0.2
Complaints-To: groups-abuse@google.com
Injection-Info: z14g2000cwz.googlegroups.com; posting-host=195.92.67.75;
   posting-account=3xsT9QwAAAC3x7TJbwl3Hj0DXQs_bISq
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:118882


lal...@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