#!user/local/bin/perl
use strict;
use warnings;
use DBI;


my $dir = '/usr/home/Scripts/Test';


# get the text files from the specified dir
opendir DIR, $dir or die "could not open directory $dir: $!";
my @files = grep /\.txt$/, readdir DIR;
closedir DIR;


my $dbh = DBI->connect("dbi:Oracle:****", "****", "*****" )
    || die( $DBI::errstr . "\n" );
$db->{AutoCommit}    = 0;
$db->{RaiseError}    = 1;
$db->{ora_check_sql} = 0;
$db->{RowCacheSize}  = 16;

foreach my $fil (@files) {

# read the ids from the files

    open IN, "$dir/$fil" or die "could not read $fil: $!";

   #my @ids = map { chomp; "'$_'" } <IN>;

    my @ids = map { "'$_'" } map { chomp; split /\s+/ } <IN>;

 
print "checking ", scalar(@ids), " ids from $fil\n";
print"@ids\n\n";
    

my $sel=$db->prepare("select (number||'/'||id) as List_ID from Info where List_ID in (" . join(', ', @ids) . ')');
   $sel->execute();
while(my $subref = $SEL->fetchrow_hashref()) {
    my $list=$subref->{'LIST_ID'};
print "$list\n";

}