#!/bin/ksh
echo "------------------------------"
echo "KSH method 1 using an array..."
echo "------------------------------"
## Max 4095 in Sun OS 5.6!
set -A my_arr `sqlplus -s scott/tiger@dev ~~EOF
set pagesize 0 feedback off verify off heading off echo off
SELECT table_name from user_tables where rownum < 6;
exit;
EOF`

echo "there are ${#my_arr[*]} elements in the array"
element=0
while [ $element -lt  ${#my_arr[*]} ]
  do
  echo "==>"${my_arr[$element]}
  let element=$element+1;
done

echo "Echo all in one command now!"
echo  ${my_arr[*]}

echo "------------------------------"
echo "KSH Method two using while read..."
echo "------------------------------"

## also works OK... sqlplus -s > tmp.txt scott/tiger@dev ~~EOF 
sqlplus -s scott/tiger@dev ~~EOF > tmp.txt
set pagesize 0 feedback off verify off heading off echo off
SELECT table_name from user_tables where rownum < 6;
exit;
EOF

while read reslt_line
do
  echo "==>"$reslt_line
done < tmp.txt 

echo "------------------------------"
echo "KSH method 3 using an array..." !!! Untested!!!
echo "------------------------------"
## Max 4095 in Sun OS 5.6!
set -A my_arr `sqlplus -s scott/tiger@dev ~~EOF
@my_sql.sql
exit;
EOF`

echo "there are ${#my_arr[*]} elements in the array"
element=0
while [ $element -lt  ${#my_arr[*]} ]
  do
  echo "==>"${my_arr[$element]}
  let element=$element+1;
done

echo "Echo all in one command now!"
echo  ${my_arr[*]}
==========================================================
#!/bin/csh 
echo "Making the sql script now to be executed..."
echo 'note Escaped $' 
cat > multi_ora_select.sql ~~EOF
        set pagesize 0 feedback off verify off heading off echo off 
        SELECT name from v$parameter where rownum < &1 ;
        exit;
EOF

echo "--------------------------------"
echo "CSH Method one using an array..."
echo "--------------------------------"

## Max 4095 in Sun OS 5.6!
set  my_arr=`sqlplus -s scott/tiger@db123 @multi_ora_select.sql 6` 
echo "there are $#my_arr elements in the array"
@ count = 1
while ( $count <= $#my_arr )
   echo $count "==>" $my_arr[$count]
   @ count++
end

echo "--------------------------------"
echo "CSH Method two using foreach..."
echo "--------------------------------"

foreach reslt_line ( `sqlplus -s scott/tiger@db123 @multi_ora_select.sql 6` )
  echo "==>"$reslt_line
end