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

You might want to consider doing this in a BEFORE DELETE trigger instead of in program logic. — Bob Jarvis - Слава Україні, Mar 22 '13 at 18:43
It isn't going to be done for every delete. Only this package. Also, triggers tend to be a bit evil. Best to avoid whenever possible. — ScrappyDev, Mar 22 '13 at 18:58
Try declaring `lc_data IS CURSOR FOR SELECT ROWID, a.* FROM table_name a` outside of your procedure, then declare your type as `TYPE some_type_list IS TABLE OF LC_DATA%ROWTYPE INDEX BY BINARY_INTEGER`. Might be worth a try. — Bob Jarvis - Слава Україні, Mar 22 '13 at 22:17
What exactly do you mean by "There are far too many records to INSERT INTO ... SELECT CRITERIA then DELETE ... CRITERIA." ? Why should the number of records prevent you from doing it in plain SQL? — Frank Schmitt, Mar 22 '13 at 22:20
Digging through some of my old posts reminded me of a potential solution for this question as well. Take a look [at this question and my answer to it](http://stackoverflow.com/questions/10213489/ora-00947-not-enough-values-while-declaring-type-globally/10213666#10213666). You might have to change your RECORD declaration to be an OBJECT, but have a look. Share and enjoy. — Bob Jarvis - Слава Україні, Mar 25 '13 at 10:45

score 3 · Accepted Answer · edited May 23 '17 at 12:22

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!

@scrappythenell: I have edited the answer. Please check. I hope it is useful to you. — Rachcha, Mar 25 '13 at 06:27

score 2 · Answer 2 · answered Mar 23 '13 at 10:28

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:

insert the table columns into the audit table, possibly using direct path (APPEND hint) for efficiency
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.

answered Mar 23 '13 at 10:28

David Aldridge

51,479
8
68
96

I like the idea of temp tables in general, but they don't perform well enough on large data sets. I'm already doing an `insert into select by rowid` then `delete by rowid`, so I'm not really looking for the alternatives. I've seen what i'm looking for done before, but don't remember the exact syntax. – ScrappyDev Mar 23 '13 at 14:06
I'd be very surprised if performance doesn't beat a PL/SQL-based method -- I've never seen a problem with large GTT myself. Your choice though. – David Aldridge Mar 23 '13 at 14:43
We are working on billions of records. It's not exactly common volume. – ScrappyDev Mar 25 '13 at 13:13
The higher the volume, the more inclined I'd be to use a GTT rather than a pl/sql method. Of course, you'd do better with truncating or dropping partitions, but SQL is always going to beat PL/SQL. – David Aldridge Mar 25 '13 at 14:29
In practice that is not always true. Especially when you consider multi-threading. also you can't truncate if you are only deleting certain records. – ScrappyDev Mar 26 '13 at 14:05
Multi-threading can apply to SQL just as well as it can to PL/SQL, with or without parallel query. It's pretty rare that there's a need to go to those lengths though. Yes, there are of course limitation on truncation http://stackoverflow.com/questions/139630/whats-the-difference-between-truncate-and-delete-in-sql – David Aldridge Mar 26 '13 at 14:31

APC · Answer 3 · 2013-03-22T22:39:21.307

1

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.

edited Mar 22 '13 at 22:39

answered Mar 22 '13 at 22:14

APC

144,005
19
170
281

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

3 Answers3