Differences in BULK COLLECT between 9i and 10g?
in [Oracle]
|
Prev: LIKE operator on CHAR column when there is no wildcard character
Next: Enterprise manager is Not Connecting to database
From: Dereck L. Dietz on 6 Jul 2008 13:11 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;
From: Maxim Demenko on 6 Jul 2008 13:49 Dereck L. Dietz schrieb: > 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; > > Works for me on 9.2.0.8 SQL> select * from v$version where rownum <=1; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production SQL> set serveroutput on SQL> declare 2 type emp_t is record( 3 empno number, 4 ename varchar2(30) 5 ); 6 type t_emp_t is table of emp_t; 7 l_emp_t t_emp_t:= t_emp_t(); 8 begin 9 select empno,ename bulk collect into l_emp_t 10 from emp 11 where deptno=20; 12 for i in 1..l_emp_t.COUNT loop 13 dbms_output.put_line(l_emp_t(i).ename); 14 end loop; 15 end; 16 / SMITH JONES SCOTT ADAMS FORD PL/SQL procedure successfully completed. Best regards Maxim
From: Shakespeare on 7 Jul 2008 04:48 "Dereck L. Dietz" <dietzdl(a)ameritech.net> schreef in bericht news:g%6ck.11354$cW3.9454(a)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
From: Shakespeare on 7 Jul 2008 05:09 "Shakespeare" <whatsin(a)xs4all.nl> schreef in bericht news:4871d85f$0$14355$e4fe514c(a)news.xs4all.nl... > > "Dereck L. Dietz" <dietzdl(a)ameritech.net> schreef in bericht > news:g%6ck.11354$cW3.9454(a)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
From: DA Morgan on 7 Jul 2008 17:04
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(a)x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org |