Re: Shell script to catch PL/SQL return values

From: Paul Foerster <paul.foerster_at_gmx.net>
Date: Wed, 28 Jun 2006 10:08:51 +0200
Message-ID: <4gerojF1n7l1iU2_at_individual.net>


Hi send.vamsi_at_gmail.com,

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

... how's something like this?

====================================8<------------------------------------

#!/bin/bash

OUTPUT=$(sqlplus -s '/ as sysdba' <<-EOF

    set heading off feedback off serveroutput on trimout on pagesize 0

    select instance_name from v\$instance;     select version from v\$instance;

    declare

        i number := 0;
    begin

        while i < 3 loop
            dbms_output.put_line(i);
            i := i+1;
        end loop;

    end;
    /
EOF
)

INSTANCE=$(echo $OUTPUT | awk '{ print $1 }') VERSION=$(echo $OUTPUT | awk '{ print $2 }')

ARG1=$(echo $OUTPUT | awk '{ print $3 }')
ARG2=$(echo $OUTPUT | awk '{ print $4 }')
ARG3=$(echo $OUTPUT | awk '{ print $5 }')

echo "Database: $INSTANCE"
echo "Version:  $VERSION"
echo "Arg1:     $ARG1"
echo "Arg2:     $ARG2"
echo "Arg3:     $ARG3"

====================================8<------------------------------------

Note the escaped dollar sign. The output then looks something like this:

Database: MYDB
Version: 10.2.0.1.0

Arg1:     0
Arg2:     1
Arg3:     2

Also note that you don't need a spool file...

-- 
cul8er

Paul
paul.foerster_at_gmx.net
Received on Wed Jun 28 2006 - 10:08:51 CEST

Original text of this message