#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– codeforester2017-02-23 04:59:32 +00:00Commented Feb 23, 2017 at 4: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– grail2017-02-23 05:41:42 +00:00Commented Feb 23, 2017 at 5:41
Add a comment
|
2 Answers
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
Comments
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
See also:
BashFAQ001 to understand why
forloop is not the best way to read text lines from a file.
1 Comment
Rohith
Problem was indentation..!
