How do I use SQL to query based off file data?

UNIX for Beginners Q & A UNIX for Dummies Questions & Answers
1

This is basically what I want to do:

I have a file that contains single lines of IDs.
I want to query the oracle database using these IDs
to get a count of which ones match a certain condition.
the basic idea is:

cat myfile |
while read id
do
$id in select count(PC.ptcpnt_id)
from ptcpnt_cmpsit PC
where
PC.gndr_type = 'F'
;
done

Hopefully, you get the gist.

2

so you want to use that ID in sql query??
is so

while read ID ; do 
sqlplus -s username/passwd << EOF
select count(PC.ptcpnt_$ID)
from ...
..
..;
exit;
EOF
done < myfile
3

Thanks! -- I will try this.

---------- Post updated at 10:56 AM ---------- Previous update was at 08:27 AM ----------

OK -- this is what I did:

cat .sum >> recsum
sort -u -o recsum.sort recsum
print "* Total Records"| tee -a ${logfile}
while read id ; do
sqlplus -s ${lgn} << EOF | tee -a ${logfile}
select count(PC.tran_nbr)
from ptcpnt_cmpsit PC
where
PC.ptcpnt_id = $id
and PC.gndr_type = 'F'
;
exit;
EOF
done < recsum.sort
print "\n${sn}: COMPLETE [$(date)]:
Output appended to log file [${logfile}]
" | tee -a ${logfile}
echo > recsum

Assume I have a couple of files named *.sum in my directory.
My output is going to the logfile, but it is also going to the screen:
COUNT(PC.PTCPNT_ID)
----------------------
0
1 row selected.

COUNT(PC.PTCPNT_ID)
----------------------
1
1 row selected.

COUNT(PC.PTCPNT_ID)
----------------------
0
1 row selected.

I don't want it to go to the screen. It also seems to be in an infinite loop. It never ends. This is an example of a couple of rows in my file (total rows around 5000):

600010001
600010060
600010065
600010070
600010090
600010120

4

thats because you are using tee command(use only >>)
read man page of tee.. tee command will display the o/p on screen and redirect it to file also..

5

Thanks -- that took care of it. The while loop is working. It is just
verrrrrrrrrrrrrrrrrrrrrrrrrrrrrry slow. I'll have to figure out a better way to
do this.
Thanks for your help.