Literal values as elements

You can use a literal value to specify an element of a collection variable. The literal values must have a data type that is compatible with the element type of the collection.

For example, the following INSERT statement inserts a literal integer into a SET(INTEGER NOT NULL) host variable called a_set:

EXEC SQL insert into table(:a_set) values (6);

The following UPDATE statement uses a derived column name (an_element) to update all elements of the a_set collection variable with the literal value of 19:

EXEC SQL update table(:a_set) (an_element) 
   set an_element = 19;

The following INSERT statement inserts a quoted string into a LIST(CHAR(5)) host variable called a_set2:

EXEC SQL insert into table(:a_set2) values ('abcde');

The following INSERT statement inserts a literal collection into a SET(LIST(INTEGER NOT NULL) host variable called nested_coll:

EXEC SQL insert into table(:nested_coll) 
   values (list{1,2,3});

Tip: The syntax of a literal collection for a collection variable is different from the syntax of a literal collection for a collection column. A collection variable does not need to be a quoted string.

The following UPDATE statement updates the nested_coll collection variable with a new literal collection value:

EXEC SQL update table(:nested_coll) (a_list)
   set a_list = list{1,2,3};

Tip: If you only need to insert or update the collection column with literal values, you do not need to use a collection host variable. Instead, you can explicitly list the literal values as a literal collection in either the INTO clause of the INSERT statement or the SET clause of the UPDATE statement.