Prepare statements that have collection variables

You use the Collection Derived Table clause with an INSERT or SELECT statement to access the collection variable. (For more information about how to use the Collection Derived Table clause and collection variables, see Complex data types.)

When you prepare a statement that manipulates the collection variable, the following restrictions apply:
  • You must specify the statement text as a quoted string in the PREPARE statement.

    For collection variables, does not support statement text that is stored in a program variable.

  • The quoted string for the statement text cannot contain any collection host variables.

    To manipulate a collection variable, you must use the question mark (?) symbol to indicate an input parameter and then provide the collection variable when you execute the statement.

  • You cannot perform multi-statement prepares if a statement contains a collection variable.
For example, the following code fragment prepares an INSERT on the a_set client collection variable: 
EXEC SQL BEGIN DECLARE SECTION;
   client collection set(integer not null) a_set;
EXEC SQL END DECLARE SECTION;

EXEC SQL prepare coll_stmt from 
   'insert into table values (1, 2, 3)';
EXEC SQL execute coll_stmt using :a_set;
Important: You must declare the collection variable as a client collection variable (a collection variable that is stored on the client computer).