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

joaotellesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

skullnobrainsCommented:
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?

the exec plan for that query  should give you the information : most likely t2.match is not indexed (i assume ids columns are primary keys) or possibly some other query that locks stuff runs at the same time.


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?

not just sed but also tr and a few others

actually, your code should output stuff on separate lines

i's assume that you read the output on a windows box ? if so winword should understand LF terminated lines

you can also try to change your echo line to something like this

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MikeOM_DBACommented:
To reduce execution time, create indexes as suggested above and...
Then, you should better try a one-pass solution (something like this):
#!/bin/bash
sqlplus -s USER/PASSWORD@SID <<! |sort -u >/var/tmp/s3_msisdn.txt
--
-- You may need to execute the following statements using 
-- an account with DBA privileges:
--
CREATE OR REPLACE DIRECTORY var_tmp AS '/var/tmp';
GRANT READ, WRITE ON DIRECTORY var_tmp TO USER;
--
-- also you could pre-execute this part:
--
DROP TABLE s3_xt;
CREATE TABLE s3_xt
(
  line_in                   VARCHAR2(80 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE oracle_loader
     DEFAULT DIRECTORY var_tmp
     ACCESS PARAMETERS
       ( RECORDS DELIMITED BY NEWLINE
     FIELDS TERMINATED BY ","  OPTIONALLY ENCLOSED BY "'"
     MISSING FIELD VALUES ARE NULL
     (
       line_in
     ) )
     LOCATION ('s3.txt')
  )
REJECT LIMIT 0
/
--
-- and only put this part to your script:
--
SET ECHO OFF TERM OFF LINE 120 TRIMS ON PAGES 0 FEED OFF VER OFF
SET SERVEROUT ON SIZE 1000000;
VAR o_csr REFCURSOR;
DECLARE
  sqltxt   VARCHAR2 (4000);
  v_or     VARCHAR2 (4) := CHR ( 9) || '   ';
BEGIN
  sqltxt   := '
SELECT t1.msisdn
  FROM tab1 t1, tab2 t2
 WHERE t1.t1_id = t2.t2_id
   AND (';

  FOR match IN (  SELECT line_in
                    FROM s3_xt
                   ORDER BY 1)
  LOOP
    sqltxt      :=
      sqltxt || v_or || 't2.match LIKE ''' || match.line_in || '%''' || CHR ( 10);
    v_or   := CHR ( 9) || 'OR ';
  END LOOP;

  sqltxt   := sqltxt || ')';
  DBMS_OUTPUT.put_line ( sqltxt);
  OPEN :o_csr FOR sqltxt;
  
END;
/
print o_csr
EXIT;
!

Open in new window

0
joaotellesAuthor Commented:
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!
0
skullnobrainsCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Shell Scripting

From novice to tech pro — start learning today.