asked on

SQL Query from file

Hi,

I have the following script that makes a SQL query in oracle database for each one of the lines from a file.

The script is working but when it reaches the line 20 of my source file it freezes. I have tested the corresponding query (directly using sql plus) and it really does not results anything or for some reason its taking hours to be completed.

I know that its hard to know what is really causing this, so, I could somehow alter the script to ignore these kind of (no) results?

As a complement, my output looks like that,

557582518789 5511992525919 5511992138836 5511999120150 556292852381 5511976954759 556191416794 5511988028118 557581961897 5511991181727
555193310888 559884679175 5515991240672 555484114019 552192212525 5519991905246 555192072439 558591677887 5515991052012 552175288668 556592424096 5511992746868 552192981366 5516992594804 552191557601 555193663660 554188447164 5512991349131 5517991525441 5511991613533 5516992823898 5511993277814 5517992248038 558592148668 5516993022367 5511976708998

555191499098 554888435050 555192092279

I know that I could use SED to edit it and transform into a one column file, there is a way to print directly like a one column?

Note that I have cases in this file with "no rows" results, but it does not cause me any problem.

The source file looks like that,

35299305
35316405
35316305
35316705
35316605
35316505
35353705
35371105
35371805
35372105
35372005
35371905
35374205
35374305
35331805
35331705

Thanks!

#!/bin/bash
while read line
do 

output=`sqlplus -s USER/PASSWORD@SID <<!
set heading off
set feedback off
select distinct T1.msisdn from TAB1 T1, TAB2 T2 where T1.t1_id = T2.t2_id and T2.match like '${line}%';
exit;
!`

echo ${output} >> /var/tmp/s3_msisdn.txt

done < '/var/tmp/s3.txt'

Open in new window

ASKER CERTIFIED SOLUTION

skullnobrains

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

MikeOM_DBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

joaotelles

ASKER

echo -ne "${output}\r\n" >> ...

It works, thanks!

To reduce execution time, create indexes as suggested above and...
Then, you should better try a one-pass solution (something like this):

That's a nice solution but I think will be too much for this situation.

Thanks!

skullnobrains

i was only answering your question, but you should definitively consider

select distinct T1.msisdn from TAB1 T1, TAB2 T2 where T1.t1_id = T2.t2_id and T2.match in (id;,id2,id3)

this will be much faster if you have an index on T2.match and a little faster if you do not.

if T2.match is not indexed, you probably had better push the source ids into a temporary indexed table and run your query with an extra join. if you do this, your rdbm will be able to do a single pass on T2 instead of multiple ones for each id