How do you select into a nested type in oracle pl/sql?

Question

I want to be able to delete by rowid then immediately insert the data being deleted in an audit table.

There are far too many records to INSERT INTO ... SELECT CRITERIA then DELETE ... CRITERIA.

I already know how to do everything just using rowid and INSERT INTO ... SELECT.

Inside package body:

TYPE some_type IS RECORD (
   row_id    ROWID,
   full_row  table_name%ROWTYPE
);
TYPE some_type_list IS TABLE OF some_type
   INDEX BY BINARY_INTEGER;

PROCEDURE do_stuff
IS
   lc_data  SYS_REFCURSOR;
   lt_recs  some_type_list;
BEGIN
   OPEN lc_date FOR
      SELECT rowid, a.*
      FROM   table_name;
   LOOP
      FETCH lc_data
      BULK COLLECT INTO lt_recs
      LIMIT 50000;
      EXIT WHEN lt_recs.COUNT = 0;
      --
      FORALL i IN lt_recs.FIRST..lt_recs.LAST
         DELETE table_name
         WHERE  ROWID = lt_recs(i).row_id;
      --
      FORALL i IN lt_recs.FIRST..lt_recs.LAST
         INSERT INTO table_name_audit VALUES lt_recs(i).full_row;
   END LOOP;
END;

If I try that i get the following error:

Line: 117 Column: 25 Type: error Text: PLS-00597: expression 'LT_RECS' in the INTO list is of wrong type

Answer 1

Oracle versions prior to 11gR2 restrict us to use BULK COLLECT into a collection (nested table or varray) of records. Read more here on Oracle Docs.

If you want to see how it is done in 11gR2, scroll down to EDIT 2 section of this answer.

An alternative tho this can be the use of separate collections for every column- an approach that is most widely used. In this you can have:

/*
TYPE some_type IS RECORD (
   row_id    ROWID,
   full_row  table_name%ROWTYPE
);
TYPE some_type_list IS TABLE OF some_type
   INDEX BY BINARY_INTEGER;
-- */
CREATE TYPE t_row_id IS TABLE OF ROWID;
CREATE TYPE t_col1 IS TABLE OF table_name.col1%TYPE;
CREATE TYPE t_col2 IS TABLE OF table_name.col2%TYPE;
CREATE TYPE t_col3 IS TABLE OF table_name.col3%TYPE;
...
...
CREATE TYPE t_colN IS TABLE OF table_name.colN%TYPE;

PROCEDURE do_stuff
IS
   lc_data  SYS_REFCURSOR;
   -- lt_recs  some_type_list;
   row_id t_row_id;
   col1 t_col1;
   col2 t_col2;
   col3 t_col3;
   ...
   ...
   colN t_colN;
BEGIN
   OPEN lc_date FOR
      SELECT rowid, a.*
      FROM   table_name;
   LOOP
      FETCH lc_data
      BULK COLLECT INTO row_id, col1, col2, col3, ..., colN
      LIMIT 50000;
      EXIT WHEN lt_recs.COUNT = 0;
      --
      FORALL i IN row_id.FIRST..row_id.LAST
         DELETE table_name
         WHERE  ROWID = row_id(i);
      --
      FORALL i IN col1.FIRST..col1.LAST
         INSERT INTO table_name_audit VALUES (col1(i), col2(i), col3(i), ..., colN(i));
   END LOOP;
END;

I have not removed many of the rows in your program in order to let you understand the changes.

EDIT : Refer to the "Restrictions on BULK COLLECT" section of the Oracle Docs link I have given above and also here.

---

EDIT #2 :

---

You have to use CREATE TYPE ... IS OBJECT instead of RECORD. Also, You need to modify the SELECT statement the way I have done when I tried it. Please see the Oracle Docs here and a StackOverflow question here for further reference.

The code I tried on my machine (runs Oracle 11g R2) is as follows:

-- SELECT * FROM user_objects WHERE object_type = 'TYPE'; CLEAR SCREEN; SET SERVEROUTPUT ON;

CREATE OR REPLACE TYPE temp_t_test AS OBJECT ( -- << OBJECT, not RECORD.
  test_id  INTEGER
, test_val VARCHAR2(50)
);
/

CREATE OR REPLACE TYPE temp_tbl_test AS TABLE OF TEMP_T_TEST;
/

DECLARE
  v_test TEMP_TBL_TEST;
BEGIN
  SELECT temp_t_test(t_id, t_val) -- << Notice the syntax
  -- I'm selecting the columns as the defined OBJECT type.
  BULK COLLECT INTO v_test
    FROM (SELECT 1 AS t_id, 'ABCD' AS t_val FROM dual
          UNION ALL
          SELECT 2, 'WXYZ' FROM dual
          UNION ALL
          SELECT 3, 'PQRS' FROM dual);

  dbms_output.put_line('Bulk Collect Successful!');
END;
/

** OUTPUT **:

TYPE temp_t_test compiled
TYPE temp_tbl_test compiled
anonymous block completed
Bulk Collect Successful!

Answer 2

I don't think that I'd take this approach at all, to be honest.

A faster method would be along the lines of performing a multitable insert:

1. insert the table columns into the audit table, possibly using direct path (APPEND hint) for efficiency
2. insert the rowid's into an on-commit-delete-rows global temporary table.

Then perform a delete against the original table using DELETE .. WHERE ROWID IN (SELECT ORIGINAL_ROWID FROM MY_GLOBAL_TEMP_TAB)

... and then commit.

Faster, and less code I think.

Answer 3

What you're trying to works in 11gR2 - what version are you on?.

The only wrong-looking thing in your post is this:

OPEN lc_date FOR
  SELECT rowid, a.*
  FROM   table_name;

It ought to be this ...

OPEN lc_data FOR
  SELECT a.rowid, a.*
  FROM   table_name a;

... but these may simply be typos you introduced when sanitizing your code to post here.