Insert row into Oracle type table of object

Question

I am able to BULK COLLECT queries into tables of type object but cannot seem to do a simple insert. Must I use a BULK collect even on a single row insert?

DROP TYPE T_TABLE;
/

CREATE OR REPLACE TYPE T_ROW AS OBJECT   
  (
    ID NUMBER(10),
    DESCRIPTION VARCHAR2(50)
  );
/ 

CREATE OR REPLACE TYPE T_TABLE AS TABLE OF T_ROW;
/

DECLARE

V_TABLE T_TABLE;
V_ROW T_ROW;

BEGIN
  INSERT INTO TABLE(V_TABLE) VALUES (1,'XXX');
  --INSERT INTO T_TABLE(V_TABLE) VALUES (T_ROW(1,'XXX'));
  --INSERT INTO TABLE(V_TABLE) VALUES (V_ROW(1,'XXX'));
END;

Answer 1

There are multiple ways to add items to a collection including:

Populate the collection in the constructor:

DECLARE
  V_TABLE T_TABLE := T_TABLE(
    T_ROW( 1, 'XXX' ),
    T_ROW( 2, 'YYY' ),
    T_ROW( 3, 'ZZZ' )
  );
BEGIN
  -- Do stuff with the collection here...
  NULL;
END;
/

or, populate the table row-by-row:

DECLARE
  V_TABLE T_TABLE;
BEGIN
  -- Constructor
  V_TABLE := T_TABLE();
  -- Extend by the default, 1 element
  V_TABLE.EXTEND;
  -- Populate that element
  V_TABLE(1) := T_ROW( 1, 'XXX' );

  -- Extend by 2 elements
  V_TABLE.EXTEND(2);
  V_TABLE(2) := T_ROW( 2, 'YYY' );
  V_TABLE(V_TABLE.COUNT) := T_ROW( 3, 'ZZZ' );

  -- Do stuff with the collection here...
END;
/

or, use SQL and BULK COLLECT INTO:

DECLARE
  V_TABLE T_TABLE;
BEGIN
  SELECT rw
  BULK COLLECT INTO V_TABLE
  FROM   (
    SELECT T_ROW( 1, 'XXX' ) AS rw FROM DUAL UNION ALL
    SELECT T_ROW( 2, 'YYY' ) FROM DUAL UNION ALL
    SELECT T_ROW( 3, 'ZZZ' ) FROM DUAL
  );
END;
/

use MULTISET UNION ALL to concatenate collections:

DECLARE
  V_TABLE1 T_TABLE := T_TABLE( T_ROW( 1, 'XXX' ), T_ROW( 2, 'YYY' ) );
  V_TABLE2 T_TABLE := T_TABLE( T_ROW( 3, 'ZZZ' ) );
  V_TABLE3 T_TABLE;
BEGIN
  V_TABLE3 := V_TABLE1 MULTISET UNION ALL V_TABLE2;
END;
/

or, if the collection is a nested table then you can do it in SQL:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE test(
  id NUMBER,
  tbl T_TABLE
) NESTED TABLE tbl STORE AS test_tab
/

INSERT INTO test VALUES ( 1, T_TABLE() )
/
INSERT INTO TABLE( SELECT tbl FROM test WHERE id = 1 ) VALUES ( T_ROW( 1, 'XXX' ) )
/
INSERT INTO TABLE( SELECT tbl FROM test WHERE id = 1 ) VALUES ( T_ROW( 2, 'YYY' ) )
/

Query 1:

SELECT t.id, tt.id, tt.description
FROM   test t
       CROSS JOIN
       TABLE( t.tbl ) tt

Results:

| ID | ID | DESCRIPTION |
|----|----|-------------|
|  1 |  1 |         XXX |
|  1 |  2 |         YYY |