put value of multiple sql statements into unix variables

Shell Programming and Scripting
1

i want to use multple sql count statements and store these count values in unix variable but in one connection only i.e. in only 1 time database should be hit ,which is the main requirement.

2

I have done it by having a shell script execute a sql file like this:

Shell script:

SQL1='select count(*) as City_Count from cities;'
SQL2='select count(*) as State_Count from states;'
sqlplus /nolog <<EOF

set serveroutput on size 100000
WHENEVER SQLERROR exit 1
WHENEVER OSERROR exit 1

connect $USERNAME/$PASSWORD@$DBNAME

@/opt/scripts/Executed_sql "$SQL1" "$SQL2"
exit
EOF

exit

Then for the file Executed_sql.sql I output to a text file:

def sel1='&1'
def sel2='&2'

SPOOL /opt/outgoing/output.txt
&sel1
&sel2
SPOOL OFF

This may need a few tweaks and I won't guarantee the coding, but it may get you started on what you need