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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting how to use data in unix text file as input to an sql query from shell
# 1  
Old 07-01-2009
how to use data in unix text file as input to an sql query from shell

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

Last edited by Yogesh Sawant; 07-01-2009 at 08:37 AM.. Reason: added code tags
# 2  
Old 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

Last edited by jayan_jay; 07-01-2009 at 07:11 AM..
# 3  
Old 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
# 4  
Old 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

# 5  
Old 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.
# 6  
Old 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?
# 7  
Old 07-01-2009
I hope the problem in filename, plz change the file below line as follows

sqlplus -s username/passwd@$dbname >> outputfile <<!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Run sql query in shell script and output data save as delimited text

I want to run sql query in shell script and output data save as delimited text (delimited text would be comma) Code: SPOOL_FILE=/pgedw/dan.txt SQL=/pgedw/dan.sql sqlplus -s username/password@myhost:port/servicename <<EOF set head on set COLSEP , set linesize 32767 SET TRIMSPOOL ON SET... (8 Replies)
Discussion started by: Jaganjag
8 Replies

2. UNIX for Dummies Questions & Answers

Inserting shell script input data automatically from a text file

Dear experts, I am new to linux programming. I have a shell script which i should run it on all my samples. I only define input and out put for this script. The inputs are 3 numbers(coordination numbers) which are available in a series of text file. Since i have a lots of samples, it takes a... (5 Replies)
Discussion started by: mohamadreza
5 Replies

3. Shell Programming and Scripting

Read input file and used it to SQL query

Hi All, Seeking for your assistance to read each line $1 and $2 of input file and used it to query. Ex. file1.txt(number range) 9064500000 9064599999 9064600000 9064699999 9064700000 9064799999 Database name: ranges_log a_no message 9064500001 test 9064700000 ... (7 Replies)
Discussion started by: znesotomayor
7 Replies

4. Shell Programming and Scripting

Shell scripting unable to send the sql query data in table in body of email

I have written a shell script that calls below sql file. It is not sending the query data in table in the body of email. spool table_update.html; SELECT * FROM PROCESS_LOG_STATS where process = 'ActivateSubscription'; spool off; exit; Please use code tags next time for your code and data.... (9 Replies)
Discussion started by: Sharanakumar
9 Replies

5. Shell Programming and Scripting

How can i run sql queries from UNIX shell script and retrieve data into text docs of UNIX?

Please share the doc asap as very urgently required. (1 Reply)
Discussion started by: 24ajay
1 Replies

6. Shell Programming and Scripting

Shell Script to execute Oracle query taking input from a file to form query

Hi, I need to query Oracle database for 100 users. I have these 100 users in a file. I need a shell script which would read this User file (one user at a time) & query database. For instance: USER CITY --------- ---------- A CITY_A B CITY_B C ... (2 Replies)
Discussion started by: DevendraG
2 Replies

7. Shell Programming and Scripting

Need help to run sql query from a script..which takes input from a file

I need to run sql script from shell script which takes the input from a file and contents of file will be like : 12345 34567 78657 and query will be like : select seq_nbr from bus_event where event_nbr='12345'; select seq_nbr from bus_event where event_nbr='34567'; select seq_nbr... (1 Reply)
Discussion started by: rkrish
1 Replies

8. Shell Programming and Scripting

How to use sql data file in unix csv file as input to an sql query from shell

Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s... (2 Replies)
Discussion started by: Nareshp
2 Replies

9. UNIX for Dummies Questions & Answers

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

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... (4 Replies)
Discussion started by: whoknows
4 Replies

10. UNIX for Dummies Questions & Answers

sql query results in unix shell script

Hi I want to get the a field from a SQL query into unix shell script variable. the whole situation is like this. 1. Opened a cursor to a table in DB2 databse. 2. Fetching individual rows with the help of cursor. 3. Each row has 4 fields. I want each of the field in individual shell... (1 Reply)
Discussion started by: skyineyes
1 Replies
Login or Register to Ask a Question