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>
end;
/
EOF
)
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.netReceived on Wed Jun 28 2006 - 10:08:51 CEST