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

"Shakespeare" <whatsin_at_xs4all.nl> schreef in bericht news:4871d85f$0$14355$e4fe514c_at_news.xs4all.nl...
>
> "Dereck L. Dietz" <dietzdl_at_ameritech.net> schreef in bericht
> news:g%6ck.11354$cW3.9454_at_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;
>>
>>
>
> Can you post a DESCRIBE of the domains table at both databases? Is the
> column order the same?
>
> Shakespeare
>

And the planets tables too?

Shakespeare Received on Mon Jul 07 2008 - 04:09:50 CDT