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
>
> `
>
> echo $outvar
>
>
>
> Thanks for your help on this.

look up the dbms_output package, if you really are using PL/SQL (your example only uses SQL). SQL sends its results directly to standard Out. Since PL/SQL runs on the server, you need the output package to get the results. Note there are limits to the output from PL/SQL.

send the multiple results to a file and parse that file.

So a modified version of your example:
sqlplus -s apps/apps <<EOF

set heading off feedback off verify off

select bug_number from ad_bugs where bug_number > '4289000';

exit

EOF >/usr/tmp/bugnums

## then use awk, sed, perl, or whatever to parse the results. ## watch out for header lines and the row count feedback line. perl makereport </usr/tmp/bugnums

HTH,
  ed Received on Tue Jun 27 2006 - 16:21:46 CDT