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

From: mathewbutler <mathewbutler_at_yahoo.com>
Date: Tue, 8 Jul 2008 00:47:34 -0700 (PDT)
Message-ID: <c1ee76ca-9b89-4606-8fa2-8a4e084c994a@79g2000hsk.googlegroups.com>


On Jul 6, 6:11 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> 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;

I think this functionality was introduduced in one of the 9.2 dot releases.

M. Received on Tue Jul 08 2008 - 02:47:34 CDT

Original text of this message