Looping SQL query in Bash script

Question

i am new to bash scripting and i was wondering if anyone could help me with the following.

I am trying to retrieve the competition name from a Oracle database using competition_id using the following statement:

select name, competition_type from competitions where competition_id=' ';

However i want to use a seperate text file whcih has a list competition_ids i want to identify, i want my script to find the name and type of all my ids and output the results in a txt file. this is what i have so far:

 #!/bin/bash     

 echo Start Executing SQL commands
 cat comps_ids.txt | while read ID 
 var=$ID
 do 
sqlplus "details"

<<  EOF
select name, competition_type 
from competitions 
where competition_id=$var;
exit;
EOF

I tried to add a done at the end but i get "unexpected line ending" error message. Can anyone solve this?

Many thanks in advance :)

Michael Krelin - hacker · Accepted Answer · 2011-08-25 18:47:46Z

1

I'm not sure what your command line should look like, but it's more like

sqlplus "details" <<EOF
select name, competition_type from competitions where competition_id=$val;
exit;
EOF

If your list of IDs isn't too big, it may be better idea to make a ,-separated list and single query.

answered Aug 25, 2011 at 18:47

Michael Krelin - hacker

144k25 gold badges200 silver badges177 bronze badges

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

Comments

Shizzmo · Accepted Answer · 2011-08-25 18:55:11Z

#!/bin/bash

function get_comp () {
    sqlplus -S user/pass@database << EOF
    set pagesize 0
    set feedback off
    set head off
    select name, competition_type 
        from competitions 
        where competition_id=$1;
EOF
}

for id in $* ; do
    get_comp $id
done

Put it in a file (get_comps.sh), and then call it like this

$ ./get_comps.sh < comp_ids.txt > text_file_out.txt

-S makes sqlplus quieter

The other setting make it return just your data, not row headers or anything else.

Of course the database credentials will be stored in your history, and available to other users using 'ps' or 'top'.

This is also horribly inefficient because it connects to the database for each row in your original file. If you have a lot of rows, you might try using python or ruby as their database stuff is pretty easy to use.

Collectives™ on Stack Overflow

Looping SQL query in 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