Shell Programming and Scripting

#!/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

$ 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

$

sql>exec delete_logsx(12345);
BEGIN delete_logsx(12345); END;

                 *
ERROR at line 1:
ORA-06550: line 1, column 18:
PLS-00302: component 'DELETE_LOGSX' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Beacuse there is a "*", I see this (when using set -xv):

+ read -p OUT
+ [[ * = DONE ]]
And then a LONG list of all the files in my directory.

SQL> exec efs
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

SQL>