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