Connected to oracle database
sqlplus << EOF
$CONNECTSTR
set heading off
set trimspool on
set feedback off

select ID,DATE from sysadm.TEST where VALUE = 'A' order by ID;

value_id = ID
value_date = DATE
EOF

1. Is it possible to reference the values, ID,DATE in unix shell script.
2. Is it possible to loop through the select statement incase the select statement returns 10 rows.
3. if the only option is writting the values from select statement to a flat file, then the above select statement writes the 10 rows in the flat file.

Try piping the whole select statement to a 'while read ...'. I think that should work.

hi,

i tried piping the whole thing to while read but there are still some errors:

#construct SQL statement

{$DCITS_SQL << EOF
set head off
select a.FIN,a.STAFF_NAME_X,b.FIN,b.DEPT_C from ci_5day_staff a, ci_cits_consol_dtls b where a.FIN=b.FIN;
exit
EOF
}|while read line

do
if ["$line"]#check if line is not null
then
#if line not null, parse the line into words/variables
set $line #set the line to positional variables, in this case is $1 and $2
fin ="$1" #staff fin number
name="$2" #name of staff
gway="$3"
echo '$fin,$name,$gway' >> $final
endif
done

please help? thanks!

Check the 'if' syntax. There should be a space before and after the "line".

if [ "$line" ]
then
...
fi

hi,

so glad to see ur reply

here's my error

Missing }
}: Command not found
while: Expression syntax


thanks!

The changes are marked in bold.

{
$DCITS_SQL << EOF
set head off
select a.FIN,a.STAFF_NAME_X,b.FIN,b.DEPT_C from ci_5day_staff a, ci_cits_consol_dtls b where a.FIN=b.FIN;
exit
EOF
}|while read line
do
if [ "$line" ] #check if line is not null
then 
#if line not null, parse the line into words/variables
set $line #set the line to positional variables, in this case is $1 and $2
fin ="$1" #staff fin number
name="$2" #name of staff
gway="$3" 
echo "$fin,$name,$gway" >> $final #changing single quotes to double
fi   # not endif
done

hi,

i am trying to do a unix script and this is my first time getting in touch with unix.

i am trying to query and execute the following:
====================================================

touch $dir/emailList.txt
set final = $dir/emailList.txt

#construct SQL statement

$DCITS_SQL << SQLSTAT
set line=(select a.FIN,a.STAFF_NAME_X,b.FIN,b.DEPT_C from ci_5day_staff a, ci_cits_consol_dtls b where a.FIN=b.FIN);
SQLSTAT

while read ($line)

#pipe the output to while read
do
if ["$line"]#check if line is not null
then
#if line not null, parse the line into words/variables
set $line #set the line to positional variables, in this case is $1 and $2
fin ="$1" #staff fin number
name="$2" #name of staff
gway="$3"
echo '$fin,$name,$gway' >> $final
endif
done
=======================================================
i keep having the error "line=undefined variable". any one know whats wrong with the above? many thanks in advance!