LLI Wiki — My Digital Knowledge Base

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;
/
Mastodon LLIMastodon SocialFosstodon