Oracle: copy a table and populate a collection type
Copy a table with or without data and initialise a TABLE type based on an OBJECT type.
Two common Oracle tasks: copying a table, and populating a collection (TABLE type) built on an object type.
Copy a table
Structure only (the condition 1=2 is never true, so no row is copied):
create table <target_table> as select * from <source_table> where 1=2;
Structure and data:
create table <target_table> as select * from <source_table>;
Populate a TABLE type based on an OBJECT type
The examples assume an object type T1_OBJ (two attributes) and a collection type T1_TAB of T1_OBJ. The collection is initialised with its constructor, which takes one object constructor per element.
One element:
DECLARE
v_t1 T1_TAB;
BEGIN
v_t1 := T1_TAB(T1_OBJ('Hernan',NULL));
END;
/
Several elements:
DECLARE
v_t1 T1_TAB;
BEGIN
v_t1 := T1_TAB(
T1_OBJ('Hernan',NULL),
T1_OBJ('Connor',5),
T1_OBJ('Mike',10),
T1_OBJ('Susie',NULL)
);
END;
/