Re: Calling PL-SQL block from Shell Script

From: William Robertson <william.robertson_at_bigfoot.com>
Date: 8 May 2005 16:26:31 -0700
Message-ID: <1115594791.177147.122250@f14g2000cwb.googlegroups.com>

alain wrote:
> hello
> look at dbms_output.enable
> regards
> <lalitb_at_softhome.net> a écrit dans le message de news:
> 1115272664.120370.98600_at_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.
> >

If the following is a script (or a function) it will return only the result of the PL/SQL function you pass in:

#!/bin/ksh

cmd=$*

sqlplus -s /nolog <<-end_sql | awk -F~ '/^~/ {print $2}' set feed off ver off serveroutput on size 1000000 conn un/pw_at_db
BEGIN

        DBMS_OUTPUT.PUT('~');
        DBMS_OUTPUT.PUT_LINE($cmd);

END;
/
exit
end_sql

I've not tested it a huge amount though - for example large return values that wrapped or blew DBMS_OUTPUT restrictions (or started with '~') would cause problems. I've not often come across shell scripts that needed to retrieve specific values from the database, beyond whether the batch process they started succeeded or failed. Received on Sun May 08 2005 - 18:26:31 CDT