Home » Infrastructure » Unix » Pass Values from SQL to UNIX
icon6.gif  Pass Values from SQL to UNIX [message #125891] Wed, 29 June 2005 14:55 Go to next message
pradosh
Messages: 2
Registered: June 2005
Location: Bangalore
Junior Member

Hi ,
Can anybody please help me out by telling me a way to pass values from a SQL script to a UNIX script.
The scenario is like this :
I am executing a shell script, which calls a SQL script. The SQL script retrieves multiple values from a database lookup depending on the parameters passed from the Shell script. Now the problem is , how do I pass these multiple values bcak to the Shell script.

Any suggestion would be helpfull

Thanks
Pradosh
Re: Pass Values from SQL to UNIX [message #125896 is a reply to message #125891] Wed, 29 June 2005 16:11 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
#!/bin/ksh
echo "------------------------------"
echo "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 < 4;
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 "using while read..."
echo "------------------------------"
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 < 4;
exit;
EOF

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




The output:
------------------------------
using an array...
------------------------------
there are 3 elements in the array
==>TAB1
==>TAB2
==>TAB3
Echo all in one command now!
TAB1 TAB2 TAB3
------------------------------
using while read...
------------------------------
==>TAB1
==>TAB2
==>TAB3


http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:430819636473
Re: Pass Values from SQL to UNIX [message #126059 is a reply to message #125896] Thu, 30 June 2005 13:32 Go to previous message
pradosh
Messages: 2
Registered: June 2005
Location: Bangalore
Junior Member

Hi Andrew,
Thanks for the input ..... I think I can implement something of this sort.

Thanks Again
Pradosh
Previous Topic: How to run sql in unix on oracle
Next Topic: Unix Timestamps
Goto Forum:
  


Current Time: Thu Mar 28 18:07:52 CDT 2024