iterating over results from sqlplus

Shell Programming and Scripting
1

hi all,
i am writing a ksh script, i am logging into an oracle db via sqlplus and running a select statement but i dont know how i can store the results from the sql so i can iterate over it and do more operations over it. I dont want to write a stored procedure with a cursor since i need to use the results and login to another db and do more checks depending on what data comes back.

#!/usr/bin/ksh

main() 
{
   resultset=`sqlplus -s ${DB_LOGIN}@${DB} << SQLEND

   select * from table_user;

SQLEND`

}

how do i iterate over the resultset ??

thanks.

2

Could this help you ?

#!/usr/bin/ksh
main()
{
   sqlplus -s ${DB_LOGIN}@${DB} > table_user.txt <<SQLEND
    set feed off
     set head off
     set pages 0
     set verify off
   select * from table_user;
SQLEND

while read line
do
echo $line
done < table_user.txt
}
3

Here's a neat little trick:

#  the command returns COUNT=<nbr> which is then eval'd to set a variable.
eval $(print "select 'COUNT='||count(*) as count from <schema>.<table>;
"|sqlplus -s / |grep COUNT=)

print "The count is $COUNT"

Output:

efs:/home2/myid $ test.ksh
The count is 79931
efs:/home2/myid$

If your variable contains a list (assuming a newline at the end of each line), then read it a line at a time:

# Read results a line at a time
cat $sqlresults | while read line
do
   # Process each line of SQL output.
   echo $line
done

Of course there are other ways to structure this but logically this is what you'll need to do.

If you will be calling sqlplus many times in the same script, its better to open it as a coprocess in order to keep overhead down. That may be out of scope for this question though. Here's an example I found that illustrates it better than I can explain it: Accessing SQL*Plus using a Korn Shell Coprocess