oracle-databaseplsqlrecordrowtypebulk-collect

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


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?


Solution

  • 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