Hi All,
First, thanks for taking the time to view this post.
The problem: Use Perl, DBI and DBD-Oracle to deal with Oracle collections.
My OS is WinNT with cygwin, Oracle 8.1.5, Perl 5.8.0, DBI 1.32, DBD-Oracle
1.12
Also, ActiveState Perl 5.6.1 DBI 1.30, DBD-Oracle 1.12
Two Perl installs - no noticable differences.
Oracle Objects look like:
SQL> desc imemine
Name Null? Type
----------------------------------------------------- -------- ------------
------------------------
C1 VARCHAR2(30)
C2 NUMBER
KEY NEST_T
SQL> desc nest_t
nest_t TABLE OF COT
Name Null? Type
----------------------------------------------------- -------- ------------
------------------------
T1 VARCHAR2(30)
T2 NUMBER
T3 DATE
SQL> desc cot
Name Null? Type
----------------------------------------------------- -------- ------------
------------------------
T1 VARCHAR2(30)
T2 NUMBER
T3 DATE
And table imemine contains:
SQL> select * from imemine;
C1 C2
------------------------------ ---------
KEY(T1, T2, T3)
----------------------------------------------------------------------------
------------------------
Armstrong 1
NEST_T(COT('Fame', 99, '07-MAR-03'), COT('Harrison', 62, '07-MAR-03'),
COT('Thoroughgood', 33, '07-M
AR-03'))
An annonymous block:
1 declare
2 ok varchar2(30);
3 o varchar2(200);
4 lnt nest_t;
5 lco cot;
6 i BINARY_INTEGER;
7 begin
8 select c1, key into ok, lnt from imemine where c2 = 1;
9 o := ok;
10 for i in lnt.first..lnt.last loop
11 lco := lnt(i);
12 o := o||' '||lco.t1||' '||lco.t2||' '||lco.t3||' ';
13 end loop;
14 dbms_output.put_line(o);
15* end;
Yields
Armstrong Fame 99 07-MAR-03 Harrison 62 07-MAR-03 Thoroughgood 33
07-MAR-03
as expected. However, trying similar op with Perl
################################snip
my $sql = "
declare
ok varchar2(30);
o varchar2(900);
lnt nest_t;
lco cot;
i BINARY_INTEGER;
begin
select c1, key into ok, lnt from imemine where c2 = 1;
o := ok;
for i in lnt.first..lnt.last loop
lco := lnt(i);
o := o||lco.t1||' '||lco.t2||' '||lco.t3||' ';
end loop;
? := o;
end;
";
my $sth = $dbh->prepare($sql) or db_error("P");
my $retval;
$sth->bind_param_inout(1, \$retval, 500);
$sth->execute or db_error("E");
print "$retval\n";
#################################snip
Yields:
ArmstrongFame 99 07-MAR-03
which is only the first record in the nested table.
Any ideas?
Thanks,
David
