Shell Programming and Scripting

#! /usr/bin/ksh

# Create co-process command processor
sqlplus -S user/password@db |&

# Send SQL to co-process
# Note: FOLD_AFTER causes all columns in the row to be
# printed as individual rows
# Also note the "sql complete" prompt which acts 
# as a marker to end the read loop.  Otherwise
# the loop will expect more output from Oracle.
print -p "
SET FEEDBACK OFF VERIFY OFF ECHO OFF PAGES 0
COLUMN Array_Element FOLD_AFTER
SELECT 'some_start_of_row_marker' Array_Element
      ,'Owner:'||owner Array_Element
      ,'Table:'||table_name Array_Element
      ,'some_end_of_row_marker' Array_Element
FROM all_tables
WHERE ROWNUM < 5
/
PROMPT sql complete
"
# Make certain that "read" reads the whole line of text
IFS='
'
# Loop through the results
while read -p LINE
do
    case "$LINE" in
        # Look for marker and break loop
        sql\ complete) 
            break 
        ;;

        ORA-*|SP2-*)
            print "Error occured"
            print "$LINE"
        ;;

        # Column 1; reset array
        some_start_of_row_marker*) 
            print "Construncting new array" 
            set -A the_array
            x=0
        ;;

        # Last column of row; process logic using array
        some_end_of_row_marker*) 
            print "Row finished; printing array containing row's data"
            for i in "${the_array[@]}"
            do
               print $i | nawk -F: '{printf ("%-10s:\t%-30s\n", $1, $2)}'
            done
            print
        ;;
 
        # All other lines are columns between start and end markers
        *) the_array[$x]=$LINE 
           (( x = $x + 1 ))
        ;;
    esac
done

Construncting new array
Row finished; printing array containing row's data
Owner     :     SYS                           
Table     :     DUAL                          

Construncting new array
Row finished; printing array containing row's data
Owner     :     SYS                           
Table     :     SYSTEM_PRIVILEGE_MAP          

Construncting new array
Row finished; printing array containing row's data
Owner     :     SYS                           
Table     :     TABLE_PRIVILEGE_MAP           

Construncting new array
Row finished; printing array containing row's data
Owner     :     SYS                           
Table     :     STMT_AUDIT_OPTION_MAP