Declare an insert cursor for a collection variable

An insert cursor allows you to insert one or more elements in the collection.

To declare an insert cursor for a collection variable, include the collection-derived table clause in the INSERT statement that you associate with the cursor. The insert cursor for a collection variable has the following restrictions:

It must be a sequential cursor; the DECLARE statement cannot specify the SCROLL keyword.
It cannot be a hold cursor; the DECLARE statement cannot specify the WITH HOLD cursor characteristic.

If you need to use input parameters, you must prepare the INSERT statement and specify the prepared statement identifier in the DECLARE statement.

You can use input parameters to specify the values in the VALUES clause of the INSERT statement.

The following DECLARE statement declares the list_curs insert cursor for the a_list variable:

EXEC SQL prepare ins_stmt from
   'insert into table values';
EXEC SQL declare list_curs cursor for ins_stmt;
EXEC SQL open list_curs using :a_list;

You can then use the PUT statement to specify the values to insert. For a code fragment that includes this statement, see Insertion of many elements into a collection host variable.

Important: Whenever you use a question mark (?) in a PREPARE statement for a collection host variable in a collection-derived table, if you execute a DESCRIBE statement you must execute it after an OPEN statement. Until the OPEN statement, does not know what the collection row looks like.

The name of the collection variable in the collection-derived table clause
The following DECLARE statement declares the list_curs2 insert cursor for the a_list variable:
```
EXEC SQL prepare ins_stmt2 from
   'insert into table values';
EXEC SQL declare list_curs2 cursor for ins_stmt2;
EXEC SQL open list_curs2 using :a_list;
while (1)
   {
   EXEC SQL put list_curs2 from :an_element;
   
;

   }
```
The USING clause of the OPEN statement specifies the name of the collection variable. You can then use the PUT statement to specify the values to insert.

After you declare the insert cursor, you can open it with the OPEN statement. You can insert elements into the collection variable once the associated insert cursor is open.