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