Home » SQL & PL/SQL » SQL & PL/SQL » Howto Initialize collection containing collection? (Oracle 9.2.0.8)
Howto Initialize collection containing collection? [message #360726] Sun, 23 November 2008 09:34 Go to next message
cruiserx
Messages: 2
Registered: November 2008
Location: Seoul
Junior Member
Hi,
I need help in initializing PL/SQL collection using 'BULK COLLECT'. And I'm using Oracle 9.2.0.8.

My original code:
create or replace package pkg_test as
TYPE rowtype_sub IS RECORD (
	col1	varchar2(10),
	col2	varchar2(10),
	num3	number
);
TYPE arrtype_sub IS TABLE OF rowtype_sub INDEX BY binary_integer;

TYPE rowtype_main IS RECORD (
	var1	varchar2(10),
	var2	varchar2(10),
	sub	arrtype_sub
);
TYPE arrtype_main IS TABLE OF rowtype_main INDEX BY binary_integer;

PROCEDURE run;
end pkg_test;
/

create table tmp_test (
	var1	varchar2(10),
	var2	varchar2(10)
);

insert into tmp_test values ('AA', 'K1');
insert into tmp_test values ('BB', 'K2');
insert into tmp_test values ('CC', 'K3');

create or replace package body pkg_test as
    PROCEDURE run IS
        arrtest arrtype_main;
        idx number := 1;
    BEGIN    
        for c1 in (
            SELECT VAR1, VAR2 --, pkg_test.arrtype_sub()
            from tmp_test
        ) loop
            arrtest(idx).var1 := c1.var1;
            arrtest(idx).var2 := c1.var2;
            idx := idx + 1;
        end loop;
    END run;
end pkg_test;
        


For next calculation, sub type and variables are needed.

Using looping assignment, there's no problem.
when i change code as following, compile error occurs.

create or replace package body pkg_test as
   PROCEDURE run IS
       arrtest arrtype_main;
   BEGIN	
       SELECT VAR1, VAR2, pkg_test.arrtype_sub()
       bulk collect INTO arrtest
       from   tmp_test;
   END run;
end pkg_test;

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PL/SQL: SQL Statement ignored
6/21     PLS-00597: expression 'ARRTEST' in the INTO list is of wrong type
7/3      PL/SQL: ORA-00904: : invalid identifier


I cannot understand why this error occur.
Is it impossible to initialize sub collection with 'Bulk Collect' in this way?

Can anybody help me?

[Updated on: Sun, 23 November 2008 09:40]

Report message to a moderator

Re: Howto Initialize collection containing collection? [message #360727 is a reply to message #360726] Sun, 23 November 2008 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use persistent types:
SQL> create or replace type rowtype_sub IS object (
  2   col1 varchar2(10),
  3   col2 varchar2(10),
  4   num3 number
  5  );
  6  /

Type created.

SQL> create or replace TYPE arrtype_sub IS TABLE OF rowtype_sub 
  2  /

Type created.

SQL> create or replace  TYPE rowtype_main IS object (
  2   var1 varchar2(10),
  3   var2 varchar2(10),
  4   sub arrtype_sub
  5  );
  6  /

Type created.

SQL> create or replace  TYPE arrtype_main IS TABLE OF rowtype_main 
  2  /

Type created.

SQL> create or replace package pkg_test as
  2  PROCEDURE run;
  3  end pkg_test;
  4  /

Package created.

SQL> create or replace package body pkg_test as
  2     PROCEDURE run IS
  3         arrtest arrtype_main;
  4     BEGIN 
  5       SELECT rowtype_main (VAR1, VAR2, cast(null as arrtype_sub)) sub
  6         bulk collect INTO arrtest
  7         from   tmp_test;
  8     END run;
  9  end pkg_test;
 10  /

Package body created.

Regards
Michel
Re: Howto Initialize collection containing collection? [message #360729 is a reply to message #360727] Sun, 23 November 2008 11:24 Go to previous message
cruiserx
Messages: 2
Registered: November 2008
Location: Seoul
Junior Member
Thank you Michel for answer.
Your code works well! Smile
I missed boundary between sql <-> pl/sql

Thanks and Regards,
Steve.
Previous Topic: PLS-00382: expression is of wrong type
Next Topic: Interaction between type inheritance and nested tables
Goto Forum:
  


Current Time: Sat Dec 10 08:50:01 CST 2016

Total time taken to generate the page: 0.06150 seconds