Using Oracle PL/SQL table of record with multiple %rowtype fields

Question

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?

Answer 1

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