While assisting a forum member, I recommended running SQL/Plus in a coprocess (to make database connections and run a test script) for the duration of his script rather than starting/stopping it once for every row in a file he was processing.
I recalled I made a coprocess example for folks at work that illustrates doing this plus shows several ways of returning query results into shell variables. I thought I would stick my neck out and post it here as I think the technique is useful. I'm sure folks out there have done this in different ways and will let us know if they have suggestions to improve it. At any rate I hope someone out there finds this info useful.
This works on Solaris using the Korn shell against an Oracle 10g database:
#!/bin/ksh
##
## Coprocess example. Start SQL/Plus in the background, and communicate
## with it via a pipe as long as you need it. This saves overhead of
## starting it multiple times. It stays running while we make queries to it.
##
## This example also shows how to save query results into shell variables
## using 3 different methods.
##
## print -p to send to the coprocess,
## read -p to read from it a line at a time.
##
output="" ## Output from SQL/Plus goes here.
set -f output ## Don't do filename expansion on this variable. For when an error
## from SQL/Plus contains a splat (*).
integer rc ## Holds a return code.
typeset -r ERRFILE=$0.err ## Define an error file.
typeset -r EOF="DONE" ## Text used to indicate the end of SQL/Plus output.
## Create the error file or zero it out if it already exists.
> $ERRFILE
## Start sqlplus in a coprocess.
sqlplus -S / |&
## Exit SQL/Plus if any of the following signals are received:
## 0=normal exit, 2=interrupt, 3=quit, 9=kill, 15=termination
trap 'print -p "exit"' 0 2 3 9 15
## Send commands to SQL/Plus.
print -p "set heading off;"
print -p "set feedback off;"
print -p "set pagesize 0;"
print -p "set linesize 500;"
##
## Send a query to SQL/Plus. It is formatted so we can set a shell variable.
##
print -p "select 'COUNT1='||count(*) as count from dual;"
print -p "prompt $EOF" ## This is an indicator that we reached the end
## of selected data. When we read a DONE from
## the coprocess we know we have no more data.
## Read the output from the coprocess a line at a time. When DONE
## is read, that indicates the end of output.
while read -p output
do
if [[ "$output" == "$EOF" ]]; then
break
else
## eval forces the shell to evaluate the line twice. First, replacing
## "$output" with "COUNT1=99999", then again which creates and sets
## a variable.
eval $output
fi
done
##
## Send another query to the same running sql/plus coprocess.
##
print -p "select 'COUNT1_DATE='|| sysdate as count_date from dual;"
print -p "prompt $EOF"
while read -p output
do
if [[ "$output" == "$EOF" ]]; then
break
else
eval $output
fi
done
##
## Send yet another query to the same running sql/plus coprocess. This one
## returns several values at once which eval handles as long as they are separated
## by ';'. This way you could read multiple values from the database in one query
## and set shell variables for them all.
##
print -p "select 'COUNT2='||count(*)||';COUNT2_DATE='||sysdate from dual;"
print -p "prompt $EOF"
while read -p output
do
if [[ "$output" == "$EOF" ]]; then
break
else
eval $output
fi
done
##
## You could also read a row into an array for cases where you may need
## to process multiple rows returned.
##
## First create a pipe-separated list of values. Don't use the deault IFS of a space as
## data returned could contain spaces. Use a character not likely in the data.
##
print -p "select count(*)||'|'||sysdate from dual;"
print -p "prompt $EOF"
while read -p output
do
if [[ "$output" == "$EOF" ]]; then
break
else
IFS="|" ## Set the Input Field Separator.
set -A output_array $output ## Create an array. It parses
## automatically on the IFS character.
fi
done
##
## Deliberately cause an error to show error handling. Call a procedure
## that does not exist.
##
print -p "exec efs"
print -p "prompt $EOF"
while read -p output
do
if [[ "$output" == "$EOF" ]]; then
break
fi
##
## If unexpected output, log it to an error file and exit. In this case, the
## error from SQL/Plus will start with "BEGIN" followed by any number of
## characters.
##
print "$output" | grep '^BEGIN.*' >/dev/null # Does output start with BEGIN?
rc=$?
if (( $rc == 0 )) then # It does.
print -p "prompt $EOF" # The prompt DONE after the exec does not
# seem to print when an error happens so
# we have to issue it again here.
while [[ "$output" != "$EOF" ]]
do
print "$output" >> $ERRFILE
read -p output
done
#print -p "exit"
#exit 1 # This should really exit but for demo
# purposes we will look the other way.
else # It doesn't so use it.
eval $output
fi
done
print "COUNT1 count is $COUNT1"
print "COUNT1 date is $COUNT1_DATE\n"
print "COUNT2 count is $COUNT2"
print "COUNT2 date is $COUNT2_DATE\n"
print "Array count3: ${output_array[0]}"
print "Array date3: ${output_array[1]}"
print "\n\nContents of error file:"
cat $ERRFILE
# Close the coprocess. Technically this is handled by the trap.
print -p "exit"
exit 0
Output:
$ gcw3.sh
COUNT1 count is 1
COUNT1 date is 03-AUG-2011
COUNT2 count is 1
COUNT2 date is 03-AUG-2011
Array count3: 1
Array date3: 03-AUG-2011
Contents of error file:
BEGIN efs; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'EFS' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
$