I tried to pass an array to a stored procedure, as mentioned in the DBD::Oracle documentation, section "SYS.DBMS_SQL datatypes", but got the message "bind_param_inout needs a reference to a scalar value"
Thanks for any help. The whole output of my script is
DBI version 1.609
DBD::Oracle version 1.23
Checking ORA_VARCHAR2_TABLE example
bind_param_inout needs a reference to a scalar value at ora_arrays.pl line 39.
Checking ORA_NUMBER_TABLE example
bind_param_inout needs a reference to a scalar value at ora_arrays.pl line 73.
and the script itself is
#!/usr/bin/perl -w
use strict ;
use Data::Dumper ;
use DBI ;
use DBD::Oracle qw(:ora_types) ;
my $dbh = DBI->connect( "DBI:Oracle:$ENV{'ORACLE_SID'}",
$ENV{'ORA_USER'},$ENV{'ORA_PASS'},
{ RaiseError => 1,
PrintError => 0,
AutoCommit => 0,
} )
or die "Cannot connect to Oracle: $DBI::errstr" ;
print "DBI version $DBI::VERSION\n" ;
print "DBD::Oracle version $DBD::Oracle::VERSION\n" ;
eval {
print "Checking ORA_VARCHAR2_TABLE example\n" ;
my $statement='
DECLARE
tbl SYS.DBMS_SQL.VARCHAR2_TABLE;
BEGIN
tbl := :mytable;
:cc := tbl.count();
tbl(1) := \'def\';
tbl(2) := \'ijk\';
:mytable := tbl;
END;
';
my $sth=$dbh->prepare( $statement );
my @arr=( "abc" );
my $cc ;
$sth->bind_param_inout(":mytable", \@arr, 10, {
ora_type => ORA_VARCHAR2_TABLE,
ora_maxarray_numentries => 100
} );
$sth->bind_param_inout(":cc", \$cc, 100 );
$sth->execute();
print "Result: cc=",$cc,"\n",
"\tarr=",Data::Dumper::Dumper(\@arr),"\n";
} ;
warn $@ if $@ ;
eval {
print "Checking ORA_NUMBER_TABLE example\n" ;
my $statement='
DECLARE
tbl SYS.DBMS_SQL.NUMBER_TABLE;
BEGIN
tbl := :mytable;
:cc := tbl(2);
tbl(4) := -1;
tbl(5) := -2;
:mytable := tbl;
END;
';
my $sth=$dbh->prepare( $statement );
if( ! defined($sth) ){
die "Prepare error: ",$dbh->errstr,"\n";
}
my @arr=( 1,"2E0","3.5" );
# note, that ora_internal_type defaults to SQLT_FLT for ORA_NUMBER_TABLE .
if( not $sth->bind_param_inout(":mytable", \@arr, 10, {
ora_type => ORA_NUMBER_TABLE,
ora_maxarray_numentries => (scalar(@arr)+2),
ora_internal_type => SQLT_FLT
} ) ){
die "bind :mytable error: ",$dbh->errstr,"\n";
}
my $cc=undef;
if( not $sth->bind_param_inout(":cc", \$cc, 100 ) ){
die "bind :cc error: ",$dbh->errstr,"\n";
}
if( not $sth->execute() ){
die "Execute failed: ",$dbh->errstr,"\n";
}
print "Result: cc=",$cc,"\n",
"\tarr=",Data::Dumper::Dumper(\@arr),"\n";
} ;
warn $@ if $@ ;
