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

rdhanek · #1 (**permalink**) 07-01-2009

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

Code:

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 :

Code:

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

Thanks in advance

jayan_jay · #2 (**permalink**) 07-01-2009

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

Ramesh_srk · #3 (**permalink**) 07-01-2009

Try this,

#!/bin/bash

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

rdhanek · #4 (**permalink**) 07-01-2009

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?

Code:

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

jayan_jay · #5 (**permalink**) 07-01-2009

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.

rdhanek · #6 (**permalink**) 07-01-2009

I tried something like this

Code:

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

Code:

#!/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?

Ramesh_srk · #7 (**permalink**) 07-01-2009

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

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

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Using Shell Script to get data from text file	jermaine4ever	Shell Programming and Scripting	1	03-13-2009 08:58 PM
shell script to read data from text file and to load it into a table in TOAD	pallavishetty	Shell Programming and Scripting	1	10-31-2008 04:15 AM
How to fetch data from a text file in Unix	shikhakaul	Shell Programming and Scripting	4	01-25-2008 11:20 AM
Need Shell Script to upload data from Text file to Oracle database	chandrashekharj	Shell Programming and Scripting	6	03-26-2007 03:21 AM
How to input username on text file into finger command on shell script	Micz	Shell Programming and Scripting	3	11-08-2005 02:38 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: