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

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

Use 'fi' instead of 'endif'
Please post any errors you get for that would give a clue where the issue is.

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!
the sql is finally returning something but the while loop is still not working...

error message:

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
}: Command not found
while: Expression syntax

I feel it could be due to the information that is coming out of the sqlplus. Are you using the '-s' option for sqlplus. Like

sqlplus -s $USER/$PASS@$DB <<EOF

What does your variable '$DCITS_SQL' contain?

hi,

pardon me...because I have no idea what is sqlplus and -s...

$DCITS_SQL is set to a path that points to another script that runs the db

Without the -s option, you will get display like this while connecting

and display like this while disconnecting,

With the -s option, this display gets surpressed. It is basically used in scripts to get only the sql statement output and to avoid all unwanted information.
If you can check the script that your '$DCITS_SQL' points to, you will be able to determine if that is the cause.

mine is without the -s

========================================================
SQL*Plus: Release 9.2.0.5.0 - Production on Mon Apr 30 10:10:42 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

sorry but i dunno whether its the sql the cause of the error because the database is returning the results but its the while loop having error...
kindly advise...thanks!

Get rid of the parenthesis and $ around "line"

while read line

hi,

i tried...its doesn't work as well...

==========================================================
{$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
fi
done

Is this the entire piece of code that you are running? Where is the variable "$final" initialised? Please change the below in the code

EOF} | while read line

to

EOF
} | while read line

I hope you are doing that. If you have additional code other than this, check if the error has something to do with that. If you donot use the '-s' option, you have to handle the information that comes from the sqlplus. Otherwise, it could give confusing results. Otherwise, you can spool the output of the sql to a file.

set head off
spool $filename
select .......
spool off
exit

This will have the entire output of the sql in that file and you can try to manipulate that file. Check and see what suits you best.

hi,

i tried but its still not running...not too sure if i am doing the correct way

$DCITS_SQL << EOF
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON VERIFY OFF TIMING OFF TERMOUT OFF
spool $tmpfile
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;
spool off
exit
EOF

while read $tmpfile

do
	if [ "$tmpfile" ]#check if line is not null
	then 
		#if line not null, parse the line into words/variables
		set $tmpfile #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

fi
done

error -> undefined tmpfile

Change the while sytanx from

to

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

fi
done <$tmpfile

hi,

thanks for your help but the tmpfile is still undefined variable...

Have you defined your tmpfile variable, like

tmpfile=/some/dir/tmpfile

this should be done at the beginning of the script.

yup i did... seriously no idea where went wrong =(