loop sql query in a bash script

Question

I need to loop a oracle sqlplus query using bash.

my scenario is like this. I have a set of names in a text file and i need to find out details of that names using a sqlplus query.

textfile.txt content:

john
robert
samuel
chris

bash script

#!/bin/bash

while read line
do
/opt/oracle/bin/sqlplus -s user@db/password @query.sql $line
done < /tmp/textfile.txt

sql query: query.sql

set verify off
set heading off
select customerid from customers where customername like '%&1%';
exit

problem is when I run the script I get errors like

SP2-0734: unknown command beginning "robert..." - rest of line ignored.

can someone tell me how to solve this?

usually it's better to perform one big query and avoid loops over sql — bw_üezi
– bw_üezi, Commented Feb 1, 2011 at 6:17
Indeed. It looks like the OP is trying to re-invent a stored procedure kept in a text file. — lll
– lll, Commented Feb 1, 2011 at 6:22

Michael Ballent · Accepted Answer · 2011-02-01 08:04:18Z

2

The way I do this all the time is as follows:

#!/bin/bash

cat textfile.txt |while read Name
do
sqlplus -s userid/password@db_name > output.log <<EOF
set verify off 
set heading off 
select customerid from customers where customername like '%${Name}%'
/
exit
EOF

Bash will auto magically expand ${Name} for each line and place it into the sql command before sending it into sqlplus

edited Feb 1, 2011 at 8:04

answered Feb 1, 2011 at 7:47

Michael Ballent

1,0889 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Benoit · Accepted Answer · 2011-02-01 06:24:09Z

1

Do you have set define on ? Is your wildcard & ? You could check glogin.sql to know.

And yes, establishing n connections to pass n queries is probably not a good solution. Maybe it's faster for you to develop and you will do that one time, but if not, you should maybe think of crafting a procedure.

answered Feb 1, 2011 at 6:24

Benoit

79.7k24 gold badges226 silver badges241 bronze badges

Collectives™ on Stack Overflow

loop sql query in a bash script

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related