Home » Infrastructure » Unix » Retriving multiple rows into shell variables.
Retriving multiple rows into shell variables. [message #97416] Fri, 26 April 2002 09:32 Go to next message
Shashikanth Durgavajjhala
Messages: 2
Registered: April 2002
Junior Member
From a table, I need to retrieve Partition names on a Select criteria basis. For every Partition name retrieved I need to export the Partition using EXP utility. So I want to write a shell scrip for this.
I knwo how to retrieve coloumns into Shell variables, but how do I retrieve multiple rows?
Re: Retriving multiple rows into shell variables. [message #97428 is a reply to message #97416] Sat, 04 May 2002 06:27 Go to previous messageGo to next message
shiva prakash
Messages: 2
Registered: May 2002
Junior Member
Hi Shashikanth,

May be you could spool all (multiple) the rows into a temporary file, and then read the partitions from the file one by one and export them accordingly, within the shell script.

Prakash.
Re: Retriving multiple rows into shell variables. [message #97445 is a reply to message #97416] Fri, 10 May 2002 11:30 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Try these approaches. Just replace the ==< with your export commands...
NOTE: Replace ~~ with two < (I can't post two < next to each other).

#!/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
Previous Topic: Re: Error connecting to db : Oracle 8.1.7
Next Topic: Calling stored procedures from unix scripts
Goto Forum:
  


Current Time: Fri Mar 29 10:13:37 CDT 2024