Sql – loop sql query in a bash script

bashloopsoraclesqlsqlplus

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?

Best Answer

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

Related Topic