Execute multiple SQL scripts from single SQL Plus connection

Shell Programming and Scripting
1

Hi!
I would like to do a single connection to sqlplus and execute some querys.
Actually I do for every query one connection to database

i.e

echo 'select STATUS from v$instance;
exit' > $SQL_FILE

sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT

echo 'select VERSION from v$instance;
exit' > $SQL_FILE

sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT

echo 'select VERSION from v$instance;
exit' > $SQL_FILE

sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT

is it posible?
thanks

2

Why don't you do this?

echo "select STATUS from v$instance;
select VERSION from v$instance;
select VERSION from v$instance;
" > $SQL_FILE

sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT
3

I need to save the result in variables or files with the same name ($SELECT_RESULT)

4

anybody know?

5

This sentence does not make any sense. Also your second and third queries are the same.

Please give a good clear example making it clear what is a file and what is a variable. Please make is clear whether any or all of them are different for each query.

You could combine the queries into one query:

select version,status from v$instance;

Also personally I would use an Oracle "spool" command to write the results to a file (i.e. not a Shell redirect).

6

This has been tested on Solaris using dtksh and Oracle 10g.

Construct the query so it returns rows as name=value, so they can be used in the shell script.
Read the query output a line at a time.
for each row returned, if it is not null, use eval to make it a shell variable.

#!/usr/dt/bin/dtksh
# Oracle environment is assumed to be set already.

unset version  # Remove variable names from the environment if they exist.
unset status

( sqlplus -s / <<EOF
  set heading off;
  select 'version='||'1.2' from dual;
  select 'status='||'UP' from dual;
  exit;
EOF
)|
while read line
do
 if [[ -n $line ]]
   then eval $line
 fi
done

print "Version: $version"
print "Status: $status"

output:

$ sptest
Version: 1.2
Status: UP
$

Hope this helps.
Gary

7

I think it looks good. I'm going to try this.
thanks!