how to return an array of elements from oracle to shell script

Shell Programming and Scripting
1

Hi all,

I have a interresting problem. My application is as follows:

From a shell script i will conn to a oracle database and fetch few rows using a select statement.

Then i want to sort these rows and return a column (collection of values of a column from the selected results) as array back to the shellscript from which i conn to the database.

p.s. I don't want to redirect the result set into a file and use it

Please help me out in solving this problem

Thanks in advance

best regards
satya

2

I prefer to use a co-process to give me maximum control over result sets. This is one method that should help you craft a solution that meets the requirement (as I read it):

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

Here are the results:

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

The co-process can be used over and over and stays active throughout your script's execution.

Thomas

3

Hi Thomas,

Thanks a lot for ur reply, i tried it in my system and some how its not working properly the errors i got are as follows

temp.sh: line 24: syntax error near unexpected token `&'
temp.sh: line 24: `sqlplus -S $ora_userid/$ora_pwd@$ORACLE_SID |&'

this error is disappearing if remove |& part from the above mentioned line.

please help me out in knowing this. if there is any other way please let me know

Thanks in advance
satya

4

What shell are you using. The example is Korn shell. It won't work in bash.