database core encoding: WE8MSWIN1252

NLS_LANG was set like this:

export NLS_LANG='AMERICAN_AMERICA.AL32UTF8'
[download]

unset NLS_LANG
[download]

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
} ) ;
[download]

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
} ) ;
[download]

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;
...
[download]

./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 = [];
[download]

#!/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;
[download]

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

Thanks