Using Oracle PL/SQL table of record with multiple %rowtype fields
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