ORACLE: INSERT INTO Table VALUES of object type collection

Question

Having created the below types;

CREATE OR REPLACE TYPE OBJ_TYPE AS OBJECT
  ( FLAG      DATE
  , NUMB      NUMBER(2,0)
  , VARC      VARCHAR2(40 BYTE));
/
CREATE OR REPLACE TYPE TBL_OBJ_TYPE AS TABLE OF OBJ_TYPE;
/

I want to simply insert the data set into a table;

DECLARE
  DATA_SET TBL_OBJ_TYPE := TBL_OBJ_TYPE();
BEGIN
  FOR REC IN (SELECT * FROM TBL_01)
  LOOP
    DATA_SET.EXTEND;
    DATA_SET(DATA_SET.COUNT) :=
    OBJ_TYPE( 1
            , REC.TBL_01_COL1
            , REC.TBL_01_COL2);
  END LOOP;

  FORALL REC IN DATA_SET.FIRST..DATA_SET.LAST
    INSERT INTO TBL_02 
    VALUES ( DATA_SET(REC).FLAG --listed column
           , DATA_SET(REC).NUMB --listed column
           , DATA_SET(REC).VARC); --listed column
END;

This one works fine, but is it somehow possible to change the "VALUES" clause to avoid naming each attribute in the source object? I would like something like this:

VALUES DATA_SET(REC)

Any help will be much appreciated.

Answer 1

We can create tables using object type definitions:

SQL> create table TBL_02 of OBJ_TYPE
  2  /

Table created.

SQL> 

There aren't many good reasons to do this, but we can work with the types in our programs.

Here is a little example, starting with the test data

SQL> select * from tbl_01
  2  /

     COL_1 COL_2
---------- ----------------------------------------
        23 ABC
        42 XYZ

SQL> DECLARE
  2     DATA_SET TBL_OBJ_TYPE := TBL_OBJ_TYPE();
  3  BEGIN
  4     FOR REC IN (SELECT * FROM TBL_01)
  5     LOOP
  6      DATA_SET.EXTEND;
  7      DATA_SET(DATA_SET.COUNT) :=
  8      OBJ_TYPE( sysdate
  9              , REC.COL_1
 10               , REC.COL_2);
 11     END LOOP;
 12
 13      FORALL REC IN DATA_SET.FIRST..DATA_SET.LAST
 14        INSERT INTO TBL_02 
 15        VALUES DATA_SET(REC)
 16        ; 
 17  END;
 18  /    

PL/SQL procedure successfully completed.

SQL> select * from tbl_02;

FLAG            NUMB VARC
--------- ---------- ----------------------------------------
20-MAR-16         23 ABC
20-MAR-16         42 XYZ

SQL> 

Alternatively we can use a PL/SQL object defined against the target table. This uses a regular heap table for TBL_02:

DECLARE
  type tgt_nt is table of TBL_02%rowtype;
  data_set tgt_nt;
BEGIN
  SELECT sysdate, COL_1, COL_2 
  bulk collect into data_set
  FROM TBL_01;

  FORALL REC IN DATA_SET.FIRST..DATA_SET.LAST
    INSERT INTO TBL_02 
    VALUES DATA_SET(REC)
    ; 
END;
/