Re: Differences in BULK COLLECT between 9i and 10g?

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sun, 06 Jul 2008 19:49:36 +0200
Message-ID: <487105B0.80203@gmail.com>

Dereck L. Dietz schrieb:
> Oracle 9i 9.2.0.1.0,
> Windows XP Pro
>
> In playing around with the 9i on my desktop I've come across something which
> has me a bit baffled.
>
> In the first code segment I have a cursor where I return a row based on a
> database table. I then BULK
> COLLECT into the nested table which is defined on the database table. This
> works just fine.
>
> However, when I try to BULK COLLECT into a record which I have defined
> myself I will receive the error
> "PLS-00597: expression 'NT_TEST' in the INTO list is of wrong type".
>
> Now, in 10g I BULK COLLECT into my own record definitions without any
> problem. From reading Steve Feurestein's "Oracle PL/SQL Programming" book
> on page 494 he mentioned that in 9i Release 2 and above you could start
> BULKCOLLECTING multiple columns (but his example does show him using a row
> based on a database table, not a user defined record).
>
> Is being able to BULK COLLECT into a table of a user-defined type something
> which was added to 10g which
> would explain why it will work with 10g but not with the 9iR2 I'm playing
> around with?
>
> CODE WHICH WORKS:
>
> TYPE t_domain_tab IS TABLE OF domains%ROWTYPE;
>
> CURSOR cr_domain_capitals
> RETURN domains%ROWTYPE
> IS
> SELECT a.domain_id,
> NULL,
> a.planet_key,
> a.upp
> FROM planets a
> JOIN (
> SELECT MAX(z.inhabitants) AS inhabitants,
> z.domain_id
> FROM planets z
> WHERE z.travel_zone IN ( C_GREEN_ZONE, C_AMBER_ZONE )
> AND z.upp_government < C_RELIGIOUS_DICTATORSHIP
> AND z.starport IN ( 'A','B','C' )
> GROUP BY z.domain_id
> ) b
> ON ( a.domain_id = b.domain_id )
> WHERE a.inhabitants = b.inhabitants;
>
> nt_domains t_domain_tab := t_domain_tab();
>
> BEGIN
>
> -- Determine domain capital worlds
>
> OPEN cr_domain_capitals;
> FETCH cr_domain_capitals BULK COLLECT INTO nt_domains;
> CLOSE cr_domain_capitals;
>
> CODE WHICH DOES NOT WORK:
>
> TYPE t_domain_capitals IS RECORD
> (
> domain_id domains.domain_id%TYPE,
> description domains.description%TYPE,
> capital domains.capital%TYPE,
> capital_upp domains.capital_upp%TYPE
> );
>
> TYPE t_domain_capital_tab IS TABLE OF t_domain_capitals;
>
> CURSOR cr_domain_capitals
> RETURN t_domain_capitals
> IS
> SELECT a.domain_id,
> NULL,
> a.planet_key,
> a.upp
> FROM planets a
> JOIN (
> SELECT MAX(z.inhabitants) AS inhabitants,
> z.domain_id
> FROM planets z
> WHERE z.travel_zone IN ( C_GREEN_ZONE, C_AMBER_ZONE )
> AND z.upp_government < C_RELIGIOUS_DICTATORSHIP
> AND z.starport IN ( 'A','B','C' )
> GROUP BY z.domain_id
> ) b
> ON ( a.domain_id = b.domain_id )
> WHERE a.inhabitants = b.inhabitants;
>
> nt_test t_domain_capital_tab :=
> t_domain_capital_tab();
>
> BEGIN
>
> OPEN cr_domain_capitals;
> FETCH cr_domain_capitals BULK COLLECT INTO nt_test;
> CLOSE cr_domain_capitals;
>
>

Works for me on 9.2.0.8

SQL> select * from v$version where rownum <=1;

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

SQL> set serveroutput on
SQL> declare

   2 type emp_t is record(

   3      empno number,
   4      ename varchar2(30)

   5 );
   6 type t_emp_t is table of emp_t;
   7 l_emp_t t_emp_t:= t_emp_t();
   8 begin

   9      select empno,ename bulk collect into l_emp_t
  10      from emp
  11      where deptno=20;
  12      for i in 1..l_emp_t.COUNT loop
  13          dbms_output.put_line(l_emp_t(i).ename);
  14      end loop;

  15 end;
  16 /
SMITH
JONES
SCOTT
ADAMS
FORD PL/SQL procedure successfully completed.

Best regards

Maxim Received on Sun Jul 06 2008 - 12:49:36 CDT