How to get ROWID when inserting into table using FORALL and RETURNING

Question

I would like to get ROWID of inserted record using RETURNING clause from FORALL INSERT statement (bulk insert) back into pl/sql table of records (associative array of records). I believe, that problem is in using table of records in FORALL and BULK COLLECT together.

See the example:

CREATE TABLE test2 (num NUMBER);
set serveroutput on

DECLARE
  TYPE r_rec IS RECORD (num NUMBER, row_id ROWID);
--  TYPE t_rid IS TABLE OF rowid INDEX BY BINARY_INTEGER;
  TYPE t_tab IS TABLE OF r_rec INDEX BY BINARY_INTEGER;
  v_tab     t_tab;
  --v_rid     t_rid;
BEGIN
  v_tab(1).num := 1.11;
  v_tab(2).num := 2.22;
  v_tab(3).num := 3.33;
  --
  FORALL i IN v_tab.first..v_tab.last
    INSERT INTO test2 (num)
    VALUES (v_tab(i).num)
    RETURNING rowid BULK COLLECT INTO v_tab(i).row_id
  ;
  FOR i IN v_tab.first..v_tab.last
  LOOP
     dbms_output.put_line('num/rowid : ' || v_tab(i).num || '/' || v_tab(i).row_id);
  END LOOP;
END;
/

It raises error: PLS-00437: FORALL bulk index cannot be used in RETURNING clause Where is a problem here? Do I need another PL/SQL table for returning ROWIDs?

Well, rowid is assigned to a record when that record is being inserted. It is possible to get it in RETURNING clause. I only need to get it back into pl/sql table+record. See the example. My questions are in context of that. — LiborStefek

XING XING · Accepted Answer · 2017-09-12T08:01:34

Do I need another PL/SQL table for returning ROWIDs?

Yes ..You can try it as below: You would need an another collection to hold the return of rowids since "FORALL bulk index cannot be used in RETURNING clause"

DECLARE
  TYPE r_rec IS RECORD (num NUMBER, row_id ROWID);

  TYPE t_tab IS TABLE OF r_rec INDEX BY BINARY_INTEGER;
  v_tab     t_tab;

  TYPE r_rw IS TABLE OF ROWID index by pls_integer;

  v_rw r_rw;

BEGIN
  v_tab(1).num := 1.11;

  v_tab(2).num := 2.22;

  v_tab(3).num := 3.33;

  --
  FORALL i IN v_tab.first..v_tab.last
    INSERT INTO test2 (num)
    VALUES (v_tab(i).num)
    RETURNING rowid BULK COLLECT INTO v_rw;

  FOR i IN v_tab.first..v_tab.last
  LOOP
     dbms_output.put_line('num/rowid : ' || v_tab(i).num || '/' ||v_rw(i));
  END LOOP;

END;
/

How to get ROWID when inserting into table using FORALL and RETURNING

1 Answers