CODEWITHSUNDEEP
oracle-databaseperldbi
Hito_kun
Hito_kun

Reputation: 992

bind_columns called with 1 values but 2 are needed

Im trying to understand why Im having the following issue.

I'm writing a little Perl script, taking some examples from here and there, adding some of my own.

What I'm doing is storing queries in a cfg file, retrieve one according to the passed argument, executing and returning a value.

use strict;
use warnings;
...
$comm = 'tablespace_critical'; # In the real script, this is a command-line argument.
...
my $query = "$CFG::CFG{'checks'}{$comm}{'query'}";
# this part was literally copied from another script
# that didn't used the strict and warnings.
#---------------------------------------------------
my $query_handle = $dbh->prepare($query);
my $result;

$query_handle->execute();
$query_handle->bind_columns( \$result);
$result=$query_handle->fetchrow_array();
$query_handle->finish;
$dbh->disconnect();
#---------------------------------------------------

The *.cfg

%CFG = (
    'tablespace_critical' => {
        'query'     => "select x\.tablespace_name,x\.used_pct from (select a\.tablespace_name,((total_space-free_space)/total_space)*100 used_pct from (select tablespace_name,sum(bytes)/(1024*1024) total_space from dba_data_files group by tablespace_name) a, (select tablespace_name,sum(Bytes)/(1024*1024) free_space from dba_free_space group by tablespace_name) b where a\.tablespace_name = b\.tablespace_name(+)) x, mon_control y where x\.tablespace_name = y\.name(+) and y\.monitoring_type (+) = 'TABLESPACE' and x\.used_pct >= decode (y\.err_value,-1,NULL,NULL,90,y\.err_value)"
    }    
)

In the original script, this worked without any hassles, but here, I'm getting bind_columns called with 1 values but 2 are needed. There are several queries stored in the config, and this is the only one giving troubles. Perl isn't my thing really, so I still have some weak concepts I'm working on, but I've spent a heavy amount of time already, so I hope you can give me a hand.

Regards.

Upvotes: 0

Views: 584

Answers (1)

Miller
Miller

Reputation: 35198

You're executing a query that is pulling two values:

select x.tablespace_name, x.used_pct from ... 
       ^^^                ^^^
       1st field          2nd field

Therefore bind_columns expects you to bind two scalars as well.

$query_handle->bind_columns( \$result);    # <-- only a single variable

If you want to pull a variable number of fields, don't bother binding columns and instead just use fetchrow_array:

$query_handle->execute();
my @result = $query_handle->fetchrow_array();

Upvotes: 2

Related Questions