How to resolve a not an ARRAY reference error

I tried the selectrow array prior to switching to a prepare but the it fails on  the bind parameters/statements with error message..

$dataextract -> bind_param(1,$firstvar);
$dataextract -> bind_param(2,$secvar);
$dataextract -> execute();

and get the error below

Select allOpen in new window

DBD::Oracle::db selectrow_array failed: ORA-01008: not all variables bound (DBD ERROR: OCIStmtExecute) [for Statement "select distinct...

Select allOpen in new window

There is something else odd going on because my code clearly has two bind values.  When you tested it, did you include the empty hash-ref?  If not, the first bind var would have been treated as the options hash (though I would have expected an error since it was not a hash-ref).

Your code should work as either:

my ($dataextract) = $dbh->selectrow_array(qq(select distinct partkey from table_A where firstvar = ? and secvar2 = ?), {}, $firstvar, $secvar)
    or die("Could not get data from $_ ".DBI->errstr);
my ($genextract) = $dbh->selectrow_array(qq(SELECT COL1, COL2, COL3  FROM table_A WHERE partkey = $dataextract))
    or die("Could not get data from $_ ".DBI->errstr);

Select allOpen in new window

or

my $sth = $dbh->prepare(qq(select distinct partkey from table_A where firstvar = ? and secvar2 = ?))
    or die("Could not prepare first query ".DBI->errstr);
$sth->execute($firstvar, $secvar);
my ($dataextract) = $sth->fetchrow_array;
$sth->finish;
$sth = $dbh->prepare(qq(SELECT COL1, COL2, COL3  FROM table_A WHERE partkey = $dataextract))
    or die("Could not prepare second query $_ ".DBI->errstr);
$sth->execute;
my @genextract = $sth->fetchrow_array;
$sth->finish;

Select allOpen in new window

In fact, using the first above, should result in very nearly the second code internally.

As someone else suggested, have you looked at refactoring into a single query?  I'm assuming the two different table_As are two distinct tables (hence changing one to table_B) - if they really are the same table, then it could be refactored differently/further.

my $genextract = $dbh->selectall_arrayref(qq{SELECT COL1, COL2, COL3  FROM table_A WHERE partkey IN (select distinct partkey from table_B where firstvar = ? and secvar2 = ?)}, {}, $firstvar, $secvar);

Select allOpen in new window

Thanks for your suggestions.  I combined the 2 queries into a single complex query.  My original intention was (and is) to separate out the 2 queries. Reason -  Should the inner query (multiple partitions exist then abort the entire process with a generic message and exit out the entire script.  With the single complex query that may not be possible?

Here's the full code...

use strict;
use warnings;
use DBI;
use Date::Manip;
use Getopt::Std;

my ($db, $user, $pw, $wkdir, $firstday, $lastday)  = @ARGV;

unless($db && $user && $pw && $wkdir && $firstday && $lastday) 
{
	print("args = $date, $db, $user, $pw, $wkdir, $firstday, $lastday\n\n");
    die("\n\n  - ERROR:  The Required number of arguments were not received..\n\n");
} 

my $dbh = DBI->connect("dbi:Oracle:$db", $user, $pw) or die("Cannot connect to ... '$db' " . DBI->errstr());

my $gen_text = $dbh->prepare(qq(SELECT regdate, regnum, regdesc FROM region_extract T WHERE partitionkey = (select distinct partition_key from region_extract where firstday = to_date(?,'YYYYMMDD') and lastday = to_date(?,'YYYYMMDD'))))																
      or die("Process Aborted - More than one partition exists for the given firstday and LastDay for table '$_'".DBI->errstr);

$gen_text -> bind_param(1,$firstday);
$gen_text -> bind_param(2,$lastday);
 $gen_text -> execute();
														              										
my @allrows;
my @columns;

my $getfilename = join '', $firstday ,'_',$lastday,'.csv';
 
 my $rc  = $gen_text->fetchrow_hashref() or $nodata = 1;    
        my @row = @{$gen_text->{NAME}};
         foreach my $col (@row)
         {
            my $name = $col;
            $name =~ s/\s+//g;
            my $data = $rc->{$name};                   
            push @allrows, $data;
            push @columns, $name;       
        }  
      my $dumpdir = "$wkdir/Region_$getfilename";  
    
    open (OUT, ">$dumpdir") or die(" - Could not open $dumpdir for export. $!");
       
#  List out all the columns/rows with header information and generating a csv outfile
				$" = ",";
			
        print OUT "@columns\n";
        print OUT "@allrows";     
       while (my $row = $gen_text->fetchrow_hashref()) 
        {
            foreach my $col (@columns)
             {                       
                print OUT $$row{$col},","; 
             }
        }    
    close(OUT);
    
#    Updates for the partition key after outfile is generated
		
	my $update_status = $dbh->prepare(qq(UPDATE region_extract SET STATUS = 'R', LSTIME = SYSDATE WHERE STATUS = 'N' and PARTITION_KEY = (select distinct partition_key from region_extract where status = 'N' and firstday = to_date(?,'YYYYMMDD') and lastday = to_date(?,'YYYYMMDD')))) 
															or die("Could not get data from table '$_'".DBI->errstr);
    
 	$update_status -> bind_param(1,$firstday); 
 	$update_status -> bind_param(2,$lastday);
        
  $update_status -> execute();

# Disconnect from the database and export out the files    

$dbh->disconnect();

Select allOpen in new window

To be more clear - the inner query should only run if one partition_key value resultss and should fail should there be more than one partition key resulted from the query.

select distinct partition_key from region_extract where firstday = to_date(?,'YYYYMMDD') and lastday = to_date(?,'YYYYMMDD'))))																
      or die("Process Aborted - More than one partition exists for the given firstday and LastDay for table '$_'".DBI->errstr 

Select allOpen in new window

I was able to use scalar and rowcount for the occurrence check after your suggestions.