Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

DBD::Oracle bind_param_inout() issue

by pme (Prior)
on Jul 16, 2020 at 16:42 UTC ( #11119413=perlquestion: print w/replies, xml ) Need Help??

pme has asked for the wisdom of the Perl Monks concerning the following question:

Update 2: Resolved, encoding setup is the reason

database core encoding: WE8MSWIN1252

NLS_LANG was set like this:

export NLS_LANG='AMERICAN_AMERICA.AL32UTF8'
And it works as expected after NLS_LANG is unset like this:
unset NLS_LANG
Update: Runnable code sample is in 'readmore'

At work I have an issue related to bind_param_inout(). If I use it just like in this example -- what is from 'man DBD::Oracle' -- then it works well.

my @arr=( "abc","efg","hij" ); $sth->bind_param_inout(":id", \\@arr, 10, { ora_type => ORA_VARCHAR2_TABLE, ora_maxarray_numentries => 100 } ) ;
But if the array is created „dynamically” then execute() gives Oracle error.
my @arr; push @arr, "abc"; push @arr, "def"; push @arr, "ghi"; $sth->bind_param_inout(":id", \\@arr, 10, { ora_type => ORA_VARCHAR2_TABLE, ora_maxarray_numentries => 100 } ) ;
Oracle error message:
PLS-00382: expression is of wrong type ORA-06550: line 2, column 11: PL/SQL: Item ignored ORA-06550: line 8, column 16: PLS-00320: the declaration of the type of this expression is incomplet +e or malformed ORA-06550: line 8, column 16: PL/SQL: ORA-00904: "TBL_SRC": invalid identifier ORA-06550: line 6, column 5: PL/SQL: SQL Statement ignored (DBD ERROR: error possibly near <*> indi +cator at char 49 in 'declare tbl_src SYS.DBMS_SQL.VARCHAR2_TABLE := <*>:id; ...
My filename is inout.pl. If I run it as ./inout.pl then it works well. But run it as ./inout.pl 1 it fails miserably. The case is based on the sample given in the manual of DBD::Oracle. In the seconds case the array is loaded from Oracle with output of a select statement.

./inout.pl 1

$VAR1 = [ 'abc', 'efg', 'hij' ]; input: $VAR1 = [ 'abc', 'efg', 'hij' ]; DBD::Oracle::st execute failed: ORA-06550: line 4, column 16: PLS-00382: expression is of wrong type ORA-06550: line 4, column 9: PL/SQL: Statement ignored ORA-06550: line 8, column 21: PLS-00382: expression is of wrong type ORA-06550: line 8, column 9: PL/SQL: Statement ignored (DBD ERROR: error possibly near <*> indicato +r at char 70 in 'DECLARE tbl SYS.DBMS_SQL.VARCHAR2_TABLE; BEGIN tbl := <*>:mytable; :cc := tbl.count(); tbl(1) := 'def'; tbl(2) := 'ijk'; :mytable := tbl; END; ') [for Statement "DECLARE tbl SYS.DBMS_SQL.VARCHAR2_TABLE; BEGIN tbl := :mytable; :cc := tbl.count(); tbl(1) := 'def'; tbl(2) := 'ijk'; :mytable := tbl; END; " with ParamValues: :cc=undef, :mytable=ARRAY(0x2519238)] at ./inout.p +l line 58. Use of uninitialized value $cc in print at ./inout.pl line 60. Result: cc= arr=$VAR1 = [];
Source of inout.pl
#!/usr/local/bin/perl use strict; use warnings; use feature qw/say/; use Data::Dumper; use DBI; use DBD::Oracle qw(:ora_types); $| = 1; $Data::Dumper::Sortkeys = 1; my $dbh = DBI->connect('dbi:Oracle:...', '...', '...'); $dbh->do("alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'" +); $dbh->do("alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI +:SS.FF'"); my $aref = $dbh->selectcol_arrayref( <<EOD ); select 'abc' from dual union select 'efg' from dual union select 'hij' from dual EOD say Dumper($aref); my @arr; if (@ARGV) { @arr = @$aref; } else { @arr = ("abc", "efg", "hij"); } say 'input: ', Dumper(\@arr); #--------------------------------------------------------------------- +---- my $statement = <<EOD; DECLARE tbl SYS.DBMS_SQL.VARCHAR2_TABLE; BEGIN tbl := :mytable; :cc := tbl.count(); tbl(1) := \'def\'; tbl(2) := \'ijk\'; :mytable := tbl; END; EOD my $sth = $dbh->prepare($statement); $sth->bind_param_inout(":mytable", \\@arr, 10, { ora_type => ORA_VARCHAR2_TABLE, ora_maxarray_numentries => 100, } ) ; my $cc; $sth->bind_param_inout(":cc", \$cc, 100); $sth->execute(); print "Result: cc=", $cc, "\n", "\tarr=", Data::Dumper::Dumper(\@arr), "\n"; exit 0;
What is the difference between array and array in perl? Any comments are welcome.

My perl version is 5.26 and DBD::Oracle version is v1.74.

Thanks

Replies are listed 'Best First'.
Re: DBD::Oracle bind_param_inout() issue
by choroba (Archbishop) on Jul 16, 2020 at 16:51 UTC
    Can you please try to use the same values? def and efg are different strings which might influence the result.

    map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
      Hi choroba,

      Sorry, it was just a typo. Please check my update in my original post. Thanks.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://11119413]
Approved by marto
Front-paged by haukex
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (3)
As of 2020-10-31 05:24 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My favourite web site is:












    Results (286 votes). Check out past polls.

    Notices?