Differences in BULK COLLECT between 9i and 10g?
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_idt_domain_capital_tab();
) b
ON ( a.domain_id = b.domain_id ) WHERE a.inhabitants = b.inhabitants; nt_test 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