Shell Programming and Scripting

'value1','value2','value3',...,'valueN'

select myfield1 from mytable
where myfield2 IN ('value1','value2','value3',...,'valueN')

select myfield1 from mytable
where myfield2 IN 
(
@aux.txt
)

args="$( cat aux.txt )"
sqlplus -s user/pass@inst << EOF
select myfield1 from mytable
where myfield2 IN ( $args );
exit
EOF

USER SQL>  2  Input truncated to 7499 characters
SP2-0027: Input is too long (> 2499 characters) - line ignored
  2  Input truncated to 7499 characters
SP2-0027: Input is too long (> 2499 characters) - line ignored
  2  Input truncated to 7499 characters
SP2-0027: Input is too long (> 2499 characters) - line ignored
  2  Input truncated to 7499 characters
SP2-0027: Input is too long (> 2499 characters) - line ignored
  2  SP2-0027: Input is too long (> 2499 characters) - line ignored
  2  Input truncated to 7499 characters
SP2-0027: Input is too long (> 2499 characters) - line ignored
  2  Input truncated to 7499 characters
SP2-0027: Input is too long (> 2499 characters) - line ignored
  2  Input truncated to 7499 characters
SP2-0027: Input is too long (> 2499 characters) - line ignored
  2  Input truncated to 7499 characters
SP2-0027: Input is too long (> 2499 characters) - line ignored
  2  SP2-0027: Input is too long (> 2499 characters) - line ignored
  2    3  Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

select myfield1 from mytable
where myfield2 IN 
(
[SQL query that returns only one field (myfield2 in this example) - the contents of the aux.txt file in table format.
)

....
WHERE myfield in (SELECT * from temp_table)
....

$
$ cat -n align.pl
     1  #!/usr/bin/perl -w
     2  use strict;
     3
     4  my ($file, $line_size) = @ARGV;                         # read input parameters
     5  open (FH, "<", $file) or die "Can't open $file: $!";
     6  chomp(my $str = <FH>);                                  # read the comma-delimited line into a variable
     7  close (FH) or die "Can't close $file: $!";
     8
     9  my $from_index = 0;                                     # set the from and to indices at the beginning
    10  my $to_index = index($str, ",", $from_index);           # of the string
    11  my $next_index;
    12  my $done = 0;
    13
    14  while ($to_index != -1) {                               # while we still have commas in the string
    15      $next_index = index($str, ",", $to_index + 1);      # determine the position of the next comma
    16      if ($next_index > $line_size - 1) {                 # and if it is higher than the line size,
    17          print substr($str, 0, $to_index + 1), "\n";     # cut the string and print it
    18          $str = substr($str, $to_index + 1);
    19          $from_index = 0;
    20      } elsif ($next_index == -1) {                       # if the next position of the comma is not found
    21          if (length($str) > $line_size) {                # and length of string is greater than the line size
    22            print substr($str, 0, $to_index + 1), "\n";   # then print till the current position of comma
    23          } else {
    24            $done = 1;                                    # otherwise print the string and we are done
    25            print $str, "\n";
    26            last;
    27          }
    28          $str = substr($str, $to_index + 1);             # reset the string so that the printed part is
    29          $from_index = 0;                                # discarded, and reset the from index as well
    30      } else {
    31          $from_index = $to_index + 1;                    # the comma position is within the line size, so
    32      }                                                   # just go to the next iteration
    33      $to_index = index($str, ",", $from_index);
    34  }
    35  if (not $done) {                                        # print only if a chunk was left out at the end
    36      print $str, "\n";
    37  }
$
$ echo "abc,def,ghi,jk,lm,n,opqr,stu,vwx,yz" > mydata.txt
$
$ cat -n mydata.txt
     1  abc,def,ghi,jk,lm,n,opqr,stu,vwx,yz
$
$ perl align.pl mydata.txt 6
abc,
def,
ghi,
jk,lm,
n,
opqr,
stu,
vwx,yz
$
$ perl align.pl mydata.txt 7
abc,
def,
ghi,jk,
lm,n,
opqr,
stu,
vwx,yz
$
$ perl align.pl mydata.txt 8
abc,def,
ghi,jk,
lm,n,
opqr,
stu,vwx,
yz
$
$ perl align.pl mydata.txt 12
abc,def,ghi,
jk,lm,n,
opqr,stu,
vwx,yz
$
$ perl align.pl mydata.txt 20
abc,def,ghi,jk,lm,n,
opqr,stu,vwx,yz
$
$

perl align.pl aux.txt 2400 > mydata.txt
echo "select myfield1 from mytable where myfield2 IN (" > exec.sql
cat mydata.txt >> exec.sql
echo ");" >> exec.sql
echo >> exec.sql
sqlplus -s user/password@db @exec.sql