Inserting into a Collection Cursor

A Collection cursor allows you to access the individual elements of a collection variable. To declare a Collection cursor, use the DECLARE statement and include the Collection-Derived Table segment in the INSERT statement that you associate with the cursor. Once you open the Collection cursor with the OPEN statement, the cursor can put elements in the collection variable.

To put elements, one at a time, into the Insert cursor, use the PUT statement and the FROM clause. The PUT statement identifies the Collection cursor that is associated with the collection variable. The FROM clause identifies the element value to be inserted into the cursor. The data type of any host variable in the FROM clause must match the element type of the collection.
Important: The collection variable stores the elements of the collection. However, it has no intrinsic connection with a database column. Once the collection variable contains the correct elements, you must then save the variable into the actual collection column with the INSERT or UPDATE statement.
Suppose you have a table called children with the following schema:
CREATE TABLE children
(
   age      SMALLINT,
   name     VARCHAR(30),
   fav_colors SET(VARCHAR(20) NOT NULL)
);
The following program fragment shows how to use an Insert cursor to put elements into a collection variable called child_colors:
EXEC SQL BEGIN DECLARE SECTION;
   client collection child_colors;
   char *favorites[]
   (
      "blue",
      "purple",
      "green",
      "white",
      "gold",
      0
   );
   int a = 0;
   char child_name[21];
EXEC SQL END DECLARE SECTION;

EXEC SQL allocate collection :child_colors;

/* Get structure of fav_colors column for untyped 
 * child_colors collection variable */
EXEC SQL select fav_colors into :child_colors
   from children where name = :child_name;
/* Declare insert cursor for child_colors collection
 * variable and open this cursor */
EXEC SQL declare colors_curs cursor for 
   insert into table(:child_colors) 
   values (?);
EXEC SQL open colors_curs;
/* Use PUT to gather the favorite-color values
 * into a cursor */
while (fav_colors[a])
{
   EXEC SQL put colors_curs from :favorites[:a];
   a++
   ...
}
/* Flush cursor contents to collection variable */
EXEC SQL flush colors_curs;
EXEC SQL update children set fav_colors = :child_colors;

EXEC SQL close colors_curs;
EXEC SQL deallocate collection :child_colors;

After the FLUSH statement executes, the collection variable, child_colors, contains the elements {"blue", "purple", "green", "white", "gold"}. The UPDATE statement at the end of this program fragment saves the new collection into the fav_colors column of the database. Without this UPDATE statement, the new collection would not be added to the collection column.