Differences in BULK COLLECT between 9i and 10g?

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Sun, 6 Jul 2008 13:11:12 -0400
Message-ID: <g%6ck.11354$cW3.9454@nlpi064.nbdc.sbc.com>


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;
Received on Sun Jul 06 2008 - 12:11:12 CDT

Original text of this message