Initialize a collection variable

You must always initialize an untyped collection variable by selecting a collection column into it. You must execute a SELECT statement, regardless of the operation you want to perform on the untyped collection variable.

Important: Selecting the collection column into the untyped collection variable provides with a description of the collection declaration.

You can initialize a collection variable by selecting a collection column into the collection variable, constructing the SELECT statement as follows:

Specify the name of the collection column in the select list.
Specify the collection host variable in the INTO clause.
Specify the table or view name (not the collection-derived table clause) in the FROM clause.

You can initialize a typed collection variable by executing an INSERT statement that uses the collection derived table syntax. You do not need to initialize a typed collection variable before an INSERT or UPDATE because has a description of the collection variable.

Suppose, for example, that you create the tab_list and tab_set tables with the statements in the following figure.

Figure 1: Sample tables with collection columns

EXEC SQL create table tab_list
   (list_col list(smallint not null));
EXEC SQL create table tab_set
( 
   id_col integer,
   set_col set(integer not null)
);

The following code fragment accesses the set_col column with a typed collection host variable called a_set:

EXEC SQL BEGIN DECLARE SECTION;
   client collection set(integer not null) a_set;
EXEC SQL END DECLARE SECTION;

EXEC SQL allocate collection :a_set;
EXEC SQL select set_col into :a_set from tab_set
   where id_col = 1234;

When you use a typed collection host variable, the description of the collection column (the collection type and the element type) is compatible with the corresponding description of the typed collection host variable. If the data types do not match, the database server will do a cast if it can. The SELECT statement in the preceding code fragment successfully retrieves the set_col column because the a_set host variable has the same collection type (SET) and element type (INTEGER) as the set_col column.

The following SELECT statement succeeds because the database server casts list_col column to a set in a_set host variable and discards any duplicates:

/* This SELECT generates an error */
EXEC SQL select list_col into :a_set from tab_list;

You can select any type of collection into an untyped collection host variable. The following code fragment uses an untyped collection host variable to access the list_col and set_col columns that Sample tables with collection columns defines:

EXEC SQL BEGIN DECLARE SECTION;
   client collection a_collection;
EXEC SQL END DECLARE SECTION;

EXEC SQL allocate collection :a_collection;
EXEC SQL select set_col into :a_collection 
   from tab_set
   where id_col = 1234;
;

EXEC SQL select list_col into :a_collection 
   from tab_list
   where list{6} in (list_col);

Both SELECT statements in this code fragment can successfully retrieve collection columns into the a_collection host variable.

After you have initialized the collection host variable, you can use the collection-derived table clause to select, update, or delete existing elements in the collection or to insert additional elements into the collection.