To read from file and putting values in WHERE clau

Question

Shell Script

#! /bin/bash
sqlplus -s <username>/<passwd>@dbname << EOF
set echo on
set pagesize 0
set verify off
set lines 32000
set trimspool on
set feedback off
SELECT *
  FROM <dbname>.<tablename1> tr
  LEFT JOIN <tablename2> t2 ON t2.id2 = tr.id1
  LEFT JOIN <tablename3> t3 ON t3.id2 = tr.id1
  LEFT JOIN <tablename4> t4 ON t4.id2 = tr.id1
 WHERE tr.TIMESTAMP > SYSDATE - 75 / 1440
   AND tr.TIMESTAMP <= SYSDATE - 15 / 1440
   AND t2.value in ( value1, value2, etc...)
 ORDER BY timestamp;

exit;  
EOF

Now, the purpose is to read 32000 values in t2.value column. These values are only numbers like 1234,4567,1236, etc. I guess i should put these numbers in a separate file and then reading that file in t2.value. But i want the SQL to be excuted only once not 32000 times. can you please advise how is that possible ? How can i get the values (separated by commas) in t2.value (by some loop, reading line probably) ?

Answer 1

you could use SQL*Loader to load those values into a temporary table that you have created in a first time with an index on its only column.

sqlldr user/password%@sid control=ctl_file

Contents of ctl_file:

load data
infile *
append
into table MY_TEMP_TABLE
fields terminated by ";" optionally enclosed by '"'
(
  column1
)
begindata
"value1"
"value2"
[...]

(double quotes are optional, and unneeded for numbers.

Then modify your query with:

AND t2 in (SELECT column1 FROM my_temp_table)

and DROP my_temp_table afterwards.

Answer 2

  #!/bin/bash
  2 t2val=$(cat /home/trnid | tr '\n' ',' | sed 's/,$//')
  3 sqlplus -s <username>/<passwd>@dbname  > /home/file << EOF
  4 set echo on
  5 set pagesize 0
  6 set verify off
  7 set lines 32000
  8 set trimspool on
  9 set feedback off
 10 SELECT *  
      FROM <dbname>.<tablename1> tr  
      LEFT JOIN <tablename2> t2 ON t2.id2 = tr.id1  
      LEFT JOIN <tablename3> t3 ON t3.id2 = tr.id1  
      LEFT JOIN <tablename4> t4 ON t4.id2 = tr.id1  
      WHERE tr.TIMESTAMP > SYSDATE - 75 / 1440  
      AND tr.TIMESTAMP <= SYSDATE - 15 / 1440  
      and t2.value in ( "t2val")  
      order by timestamp; 
 26 exit;
 27 EOF

 trnid file has total of 32000 lines (each number on separate line). The length of each number is 11 digits.  

I just happen to see different Error :

Input truncated to 7499 characters SP2-0027: Input is too long (> 2499 characters) - line ignored Input truncated to 7499 characters SP2-0027: Input is too long (> 2499 characters) - line ignored.

The previous Error I got bcoz i inserted the numbers in trnid file separated by commas and in different line. In the case, i used only the command :

 t2val=$(cat /home/trnid )

Answer 3

You can create a comma separated list from the file that contains all the numbers one per line as:

t2val=$(cat your_file_with_numbers | tr '\n' ',' | sed 's/,$//')

Next you can use this variable $t2val as:

....
and t2.value in ( "$t2val")

We are replacing the \n between the lines with a comma and deleting the last comma which as no following number as it will create a syntax error in Oracle.