how to use data in unix text file as input to an sql query from shell

Shell Programming and Scripting
1

Hi,
I have data in my text file something like this.

adams
robert
ahmed
gibbs

I want to use this data line by line as input to an sql query which i run by connecting to an oracle database from shell.
If you have code for similar scenario , please ehlp.
I want the output of the sql query to be updated into a text fiel so that i can mail the same as a report.
output expected :

Name   subject  marks
adams  maths   79
Robert Sceience 67
gibbs   maths    81

Thanks in advance

2

Try this...
for i in `cat textfile`
do
sh <dbscript-name> "$line"
done

coding for dbscript:
--------------------
sqlplus -s '<user-name>/<password>' > /dev/null << EOF
exec <your own DB process command>('$1');
commit;
EOF

3

Try this,

#!/bin/bash

exec<InputFilename
while read line
do
$ORACLE_HOME/bin/sqlplus -s username/Password <<!
select * from Tablename where Fieldname='$line'
/
!
done

4

Thanks Jay and ramesh for wuick response.
Do i need to spool the data for mailing at the end? If so how can i do that?
I am new to unix..so please dont mind.

---------- Post updated at 06:43 AM ---------- Previous update was at 06:34 AM ----------

I am trying like this. Could you please check if this is fine?

#!/bin/ksh



sqlplus -s $prodUser/$prodPasswd@$prodDatabase <<SQL


spool $LOG


select 'student name,subject,marks' from dual;

set serveroutput on size 1000000;
SELECT
 student name,subject,markst(*) count1
FROM
 student
WHERE
  student_name='$line'


if student_name is null then
null;
else
dbms_output.put_line(student_name||','||subject||','||marks);
end if;
end;
/
!
done


spool off

SQL

mailx -s "$message" rdhanek@yahoo.com
5

Make the changes to this line as
$ORACLE_HOME/bin/sqlplus -s username/Password >> TmpOutputFile <<!

then it will append the output of all the lines... Finally make use of this name to send mails.

6

I tried something like this

#!/bin/bash

exec<inputfilename
while read line
do
sqlplus -s username/passwd@$dbname >> output file <<!
select * from table where fieldname='$line'
/
!
done

But the output file just contains the details about the sqlplus command. Do i need to use sppol here?

---------- Post updated at 07:21 AM ---------- Previous update was at 07:11 AM ----------

I tried something like this

#!/bin/bash

exec<inputfilename
while read line
do
sqlplus -s username/passwd@$dbname >> output file <<!
select * from table where fieldname='$line'
/
!
done

But the output fiel only contains the details of sqlplus command and nothing else. Do i need to use spool here?

7

I hope the problem in filename, plz change the file below line as follows

sqlplus -s username/passwd@$dbname >> outputfile <<!

8

Hi Ramesh and Jay..It worked thanks a lot for your help...Btw i am getting my data in this format

 SERIAL FIRSTOCCURRENCE
---------- -------------------
NETPROTOCOL
----------------------------------------------------------------
1947430746 07/01/2009 05:18:05
FR

instead of regular format

 
SERIAL          FIRSTOCCURRENCE        NETPROTOCOL
 
1947430746   07/01/2009 05:18:05           FR
]

Can anyone help me with this formatting? or provide me link if there is already a solution in this forum?