Oracle Query results to be stored in variables

Shell Programming and Scripting
1

Hi

I would like to know if there is a way to just have one ORACLE connection established, using which we can execute different queries and store the results under different variables.

For e.g the following uses to two silent ORACLE connection to store the result under two different variables. Instead can we just be able to modify the code to use the same connection against multiple queries?

#!/bin/bash
Query1=`sqlplus -s user/pwd@host<<eof
set serveroutput on;
set feedback off;
set linesize 1000;
select name from success; --Just for e.g.
EXIT;
eof`
....Operation on $Query1

Query2=`sqlplus -s user/pwd@host<<eof
set serveroutput on;
set feedback off;
set linesize 1000;
select name from failure; --Just for e.g.
EXIT;
eof`
....Operation on $Query2

The reason for asking is that the same code is required to be repeated multiple times.

Also, can you pls explain how to read the results stored in the variable line by line.

For e.g.

if Query1 results are:

int1
int4
int5

I would like to be able to get int1 value first, get the second one int4 and get the last one int5. Just would like to know how to perform a loop and read each line.

Thanks for your help.
Ashok

2

RETVAL=`sqlplus -s user/pwd@host <<EOF
SET SERVEROUTPUT ON SIZE 100000
Declare

OUT_MSG VARCHAR2(200);

Begin

select name into OUT_MSG from table_success;

dbms_output.put_line ('KeepThis '||nvl(OUT_MSG,''));
End;
/
SET SERVEROUTPUT OFF
EXIT;
EOF`

X=`echo $RETVAL | grep KeepThis | awk '{print $2}'`
Y=`echo $RETVAL | grep KeepThis | awk '{print $3}'`
Z=`echo $RETVAL | grep KeepThis | awk '{print $4}'`

echo "The Query output is: "

echo "Query OUT_MSG 1= $X "
echo "Query OUT_MSG 2= $Y "
echo "Query OUT_MSG 3= $Z "

If you have more outputs from query, print those results in $5, $6,......

3

Hi,

This answer does not work in SQL 8i if the SQL query returns more values. Is there any other way to store multiple values in a variable?

Thanks.

4

Hi ,

First redirect the query result to a file named "query_result.txt "
as shown below

#!/bin/ksh

sqlplus -s $USERID/$USERPWD <<EOF >query_result.txt
set heading off feedback off pagesize 0 linesize 30000 trimout on ;
select data from table_name ;
exit;
EOF

####after redirecting the output to a file fetch the value line by line as shown in below

for i in `cat query_result.txt `
do

echo $i

##do what ever u want to

delete from table_name where column_name = $i ;

##note : refer the column value as $i to get the value

done

      • > Explaination :

here the refer the value to i ,this will fetch the records line by line
for eg : if the query output is

red
blue
green

here for the
1st iteration : i value := red
2nd iteration : i value := blue
and so on ...

I hope this wil help you ..

5

You can set a shell array to the SQL results as such:

$cat test.ksh
#!/bin/ksh
set -A SQL_RESULTS_ARRAY $(
sqlplus -S <<EOF
user/passwd
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF ECHO OFF SERVEROUT ON TIME OFF TIMING OFF
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY'),
       TO_CHAR(SYSDATE - 1, 'MM/DD/YYYY')
  FROM DUAL;
EOF
)

SQL_ARRAYCOUNT=${#SQL_RESULTS_ARRAY[*]}
SQL_ARRAYIDX=0

while (( $SQL_ARRAYIDX < $SQL_ARRAYCOUNT ))
do
 print "SQL_ARRAY[$SQL_ARRAYIDX]=(${SQL_RESULTS_ARRAY[$SQL_ARRAYIDX]})"
 SQL_ARRAYIDX=$(($SQL_ARRAYIDX+1))
done

exit 0
$./test.ksh
SQL_ARRAY[0]=(03/16/2009)
SQL_ARRAY[1]=(03/15/2009)
$