The untyped collection variable

An untyped collection variable provides a general description of a collection. This declaration includes only the collection keyword and the variable name.

The following lines declare three untyped collection variables:

EXEC SQL BEGIN DECLARE SECTION;
   client collection collection1, collection2;
   client collection grades;
EXEC SQL END DECLARE SECTION;

The advantage of an untyped collection host variable is that it provides more flexibility in collection definition. For an untyped collection variable, you do not have to know the definition of the collection column at compile time. Instead, you obtain, at run time, a description of the collection from a collection column with the SELECT statement.

Tip: If you know the exact data type of the collection column you want to access, use a typed collection variable.

To obtain the description of a collection column, execute a SELECT statement to retrieve the column into the untyped collection variable. The database server returns the column description (the collection type and the element type) with the column data. assigns this definition of the collection column to the untyped collection variable.

For example, suppose the a_coll host variable is declared as an untyped collection variable, as follows:

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

The following code fragment uses a SELECT statement to initialize the a_coll variable with the definition of the list_col collection column (which Sample tables with collection columns defines) before it uses the collection variable in an INSERT statement:

EXEC SQL allocate collection :a_coll;

/* select LIST column into the untyped collection variable 
 * to obtain the data-type information */
EXEC SQL select list_col into :a_coll from tab_list;

/* Insert an element at the end of the LIST in the untyped
 * collection variable */
EXEC SQL insert into table(:a_coll) values (7);

To obtain the description of a collection column, your application must verify that a collection column has data in it before it selects the column. If the table has no rows in it, the SELECT statement does not returns column data or the column description and cannot assign the column description to the untyped collection variable.

You can use an untyped collection variable to store collections with different column definitions, as long as you select the associated collection column description into the collection variable before you use the variable in an SQL statement.

Important: You must obtain the definition of a collection column for an untyped collection variable before you use the variable in an SQL statement. Before the collection variable can hold any values, you must use a SELECT statement to obtain a description of the collection data type from a collection column in the database. Therefore, you cannot insert or select values directly into an untyped collection variable.