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?
 
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
 
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.