How to resolve a not an ARRAY reference error

I am trying to reference the value of query in a subsequent query and get an error "not an array reference "on line 11.

I am extracting the partkey and assigning it to dataextract. I would like to use this value of partkey in subsequent prepare statement in the script.  I am not sure why the assignment fails.

my $dataextract = $dbh -> prepare(qq(select distinct partkey from table_A where firstvar = ? and secvar2 = ?)))
												 or die("Could not get data from $_ ".DBI->errstr);											 
 
$dataextract -> bind_param(1,$firstvar);
$dataextract -> bind_param(2,$secvar);
$dataextract -> execute();
												 
if ($dataextract) 
 {
	my $genextract = $dbh->prepare(qq(SELECT COL1, COL2, COL3  FROM table_A WHERE partkey = @$dataextract))	-- line 11															
                 or die("Could not get data from $_ ".DBI->errstr);
   ..
  ..
  ..                								
  } 
 else
   exit(1);

Open in new window

steve2312Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wilcoxonCommented:
The problem is that execute returns a value about the execution and not the selected values.

Why not use the selectrow and/or fetchrow methods?  So, line 1-6 would become:
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);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
steve2312Author Commented:
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

Open in new window

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

Open in new window

0
FishMongerCommented:
Using
if ($dataextract) 

Open in new window

does not make any sense since $dataextract is your statement handle and if that is false, then it means that your prepare statement failed and the die statement would have been executed.

After the execute statement, you need to use a selectrow array (or similar) statement and use that returned data in the next select statement.  I'd probably adjust that second statement to use an IN clause where the list of values is from your prior select statement so that you don't have to put that select statement in a loop.

In fact, you should/might be able to combine those 2 select statements into a single more complex statement instead of doing it in 2 steps.  But I'd have to run some tests to be sure.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

wilcoxonCommented:
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);

Open 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;

Open 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);

Open in new window

0
steve2312Author Commented:
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();

Open in new window

0
steve2312Author Commented:
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 

Open in new window

0
steve2312Author Commented:
I was able to use scalar and rowcount for the occurrence check after your suggestions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.