Pass values read from a file as input to an SQL query in Oracle

Question

#cat file.txt
 12354
 13456
 13498

#!/bin/bash
for i in `cat file.txt`
do
    sqlplus XXXXX/XXXXX@DB_NAME << EOF 
        select *from TABLE_NAME where id="$i"
    EOF
done

This is not working for me. Help me how I can solve this.

You didn't tell what error you are getting. I guess it is because of "EOF" being indented. — codeforester, Feb 23 '17 at 04:59
You could also change your select to use 'in' and store the data in an array. Also, if you place a hyphen before EOF you can use it indented (must be by tabs only) — grail, Feb 23 '17 at 05:41

score 2 · Answer 1 · answered Aug 03 '17 at 08:58

The solution given by @codeforester works. However I was unable to use it because it created as many DB connections as the number of lines in your file which is a potential impact.

To overcome this, I chose the below solution which may not be ideal but does the job with just one DB connection.

Considering the same data in file.txt

 12354
 13456
 13498

I used the below sed command to populate the above to a single variable "12354,13456,13498"

myvariable=$(echo "`cat file.txt | sed '$!s/$/,/g' | tr -d '\n' | tr -d ' '`")

Now below script will pass this variable to the SQL query and spool the data into a text file:

#!/bin/bash
myvariable=$(echo "`cat file.txt | sed '$!s/$/,/g' | tr -d '\n' | tr -d ' '`")
echo @myvariable
sqlplus /nolog << EOF
CONNECT user@dbname/dbpassword
SPOOL dboutput.txt
select column1 from table_name where id in ($myvariable);
SPOOL OFF
EOF

The output is stored in dboutput.txt (along with the SQL query)

cat dboutput.txt
SQL>   select column1 from table_name where id in (12354,13456,13498);

NAME
----------------------------------------------------------------------------  ----
data1
data2
data3

SQL> spool off

score -1 · Accepted Answer · edited May 23 '17 at 12:17

-1

Here is the right way to use the heredoc <<, along with the choice of while read instead of for to read the file:

#!/bin/bash

while read -r value; do
  sqlplus xxxxx/xxxxx@db_name << EOF
    select * from table_name where id='$value';
    # make sure heredoc marker "EOF" is not indented
EOF
done < file.txt

Pass values read from a file as input to an SQL query in Oracle

2 Answers2

Linked