To read from a file and put values ​​in a WHERE clause through a shell script

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

The goal now is to read 32,000 values ​​in the t2.value column. These values ​​are only numbers, such as 1234.4567.1236, etc. I think I should put these numbers in a separate file and then read this file in t2.value. But I want SQL to be excluded only once, not 32,000 times. can you advise how this is possible? How can I get the values ​​(separated by commas) in t2.value (on some loops, perhaps a read line)?

Answer 1

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

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

$t2val :

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

\n , , Oracle.

Answer 2

SQL * Loader , .

sqlldr user/password%@sid control=ctl_file

ctl_file:

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

( .

:

AND t2 in (SELECT column1 FROM my_temp_table)

DROP my_temp_table.

Answer 3

  #!/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.  

:

7499 SP2-0027: ( > 2499 ) - 7499 SP2-0027: ( > 2499 ) - .

In a previous error, I got bcoz i by inserting numbers into the trnid file, separated by commas and on another line. In this case, I used only the command:

 t2val=$(cat /home/trnid )