Re: Shell script to catch PL/SQL return values

send.vamsi_at_gmail.com wrote:
: Hello,

: I need some help from the experts on PL/SQL and Shell scripting. I need
: a shell script that runs a PL/SQL procedure and gets the values
: returned from the PL/SQL procedure into the shell variables. The PL/SQL
: procedure returns multiple values.

: I was able to assign a single return value from the SQL statement to
: the shell variable as below, but what about multiple values...

: bash-2.05$ more shellsql.sh

: #!/bin/ksh

: #set -x

: outvar=`sqlplus -s apps/apps <<EOF

: set heading off feedback off verify off

: select bug_number from ad_bugs where bug_number='4289310';

: exit

: EOF
Format the output as a shell script and then run it. (Make sure to escape the values that you don't control). Either save the output and use "source" on the output, or use "eval" directly on the sqlplus output.

i.e. sql like

	select 
		'bug_number=' || bug_number  ,
		'complainer=' || escape_func( complainer )
	from ...etc...

then either

        run the sql and either redirect or spool the output to tmp/$$

        $ source /tmp/$$

or

        $ eval `sqlplus using the sql shown above` Received on Tue Jun 27 2006 - 19:22:58 CEST