dirtdog has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, I'm a little stuck and was hoping one of you could help get me out of the mud :-). I had a script that worked until we added over 999 values to it recently. I now have about 3,000 values separated by a comma on 1 line. Unfortunately, I can't change the way the data is provided. Ultimately, I need to get a max of 999 of them into an array or some other structure so I can then perform a query from the db with those values. i.e, select * from table_name where values in ( ). If I can just get chunks of 999 stuffed into an array I could then do a "for loop" until all values have been queried and added as a new worksheet to my excel spreadsheet. It would need to be dynamic too since the values could increase or decrease over time.

Below is a snippet of the code that used to work (until we broke the 999 threshold that Oracle allows in an "IN" clause by adding over 3k comma separated values in the input record.)

open INPUT_FILE, @row[$cell_column] or die "Can't open @row[$cell_colu +mn]: $!\n"; while ( $line = <INPUT_FILE> ) { chomp $line; if ($line =~ /^#/ || $line =~ /^\s*$/) { next; } $line =~ s/^[^=]*\=//; @users = split /,/, $line; } close INPUT_FILE; $placeholders = join ( ',', ('?') x @users ); my $SQLLock=qq{ select user,name from teams where user in ( $placeholders ) }; my $sthL = $dbh->prepare($SQLLock); $sthL->execute(@users); while ( my @row = $sthL->fetchrow_array() ) { print ">@row<\n"; for($cell_column=0; $cell_column<=$#row; $cell_column+ ++){ $worksheet->write_string($i, $cell_column, @r +ow[$cell_column],$format1); } $worksheet->set_row($i, undef, undef, 0, 0, 1); $i++; } $sthL->finish;

Here is my attempt to get the split function broken up by lines of 3. ( For the example I chose a small number because it's more managable. Just need to prove out the logic, but clearly the "limit" clause of the split isn't keeping 3 values per line. This does not work.

#!/usr/bin/env perl use DBI; use POSIX 'strftime'; use strict; use Data::Dumper; my $line; my @teams; while ( $line = <DATA> ) { chomp $line; if ($line =~ /^#/ || $line =~ /^\s*$/) { next; } $line =~ s/^[^=]*\=//; @teams = map { [ split /,/, $_, 3 ] } $line; } print Dumper \@teams; __DATA__ Teams=PATRIOTS,BILLS,DOLPHINS,JETS,COWBOYS,GIANTS,EAGLES,REDSKINS,BENG +ALS,OILERS,STEELERS,BROWNS,SEAHAWKS,RAMS,49ERS,RAIDERS

Would greatly appreciate any help or assistance that could be provided. Thanks!