How can I populate a table of records which has more than one field of %rowtype using bulk collect?
my code:
drop table child_table;
drop table parent_table;
/
create table parent_table(pk number primary key);
create table child_table(pk number primary key, fk REFERENCES parent_table(pk));
/
insert into parent_table (pk) values (1);
insert into parent_table (pk) values (2);
insert into child_table (pk, fk) values (11, 1);
insert into child_table (pk, fk) values (21, 1);
insert into child_table (pk, fk) values (32, 2);
/
declare
type rec is record
(
parent parent_table%rowtype,
child child_table%rowtype
);
type tbl is table of rec;
v_table tbl := tbl();
-- this works
type tbl_parent is table of parent_table%rowtype;
v_parent_table tbl_parent := tbl_parent();
begin
-- this works
select * bulk collect into v_parent_table from parent_table;
-- this doesn't work
select * bulk collect into v_table from parent_table parent
inner join child_table child on parent.pk = child.fk;
end;
this code that doesn't work but throws the following error message:
ORA-06550: line 13, column 30:
PLS-00597: expression 'V_TABLE' in the INTO list is of wrong type
ORA-06550: line 13, column 30:
PLS-00597: expression 'V_TABLE' in the INTO list is of wrong type
ORA-06550: line 13, column 38:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 13, column 3:
PL/SQL: SQL Statement ignored
so ok, oracle says I'm using the wrong datatype and I agree. but how to fix it?
You are getting an error because the columns of the join SELECT *
are different from the column of your table.
Use a CURSOR%rowtype
:
SQL> DECLARE
2 CURSOR cc IS
3 SELECT p.pk, c.pk cpk, c.fk
4 FROM parent_table p
5 JOIN child_table c ON p.pk = c.fk;
6 TYPE tbl_join IS TABLE OF cc%ROWTYPE;
7 l_table tbl_join;
8 BEGIN
9 OPEN cc;
10 FETCH cc BULK COLLECT INTO l_table;
11 CLOSE cc;
12 END;
13 /
PL/SQL procedure successfully completed
Or don't use SELECT *
:
SQL> DECLARE
2 TYPE tbl_child IS TABLE OF child_table%ROWTYPE;
3 l_table tbl_child;
4 BEGIN
5 SELECT c.* BULK COLLECT INTO l_table
6 FROM parent_table p
7 JOIN child_table c ON p.pk = c.fk;
8 END;
9 /
PL/SQL procedure successfully completed