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

Dereck L. Dietz 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;

Doesn't surprise me.

Tom Kyte has written extensively at asktom.oracle.com about the difference. You can find his explanation by checking his website or reading his books.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org