DEALLOCATE COLLECTION statement

Use the DEALLOCATE COLLECTION statement to release memory for a collection variable that was previously allocated with the ALLOCATE COLLECTION statement.

This statement is an extension to the ANSI/ISO standard for SQL. Use this statement with ESQL/C.

Syntax


1  DEALLOCATE COLLECTION :variable
Element Description Restrictions Syntax
variable Name that identifies a typed or untyped collection variable for which to deallocate memory Must be the name of the collection variable that has already been allocated Name must conform to language-specific rules for names of variables

Usage

The DEALLOCATE COLLECTION statement frees all the memory that is associated with the collection variable that variable identifies. You must explicitly release memory resources for a collection variable with DEALLOCATE COLLECTION. Otherwise, deallocation occurs automatically at the end of the program.

The DEALLOCATE COLLECTION statement releases resources for both typed and untyped collection variables.
Tip: The DEALLOCATE COLLECTION statement deallocates memory for collection variables only. To deallocate memory for row variables, use the DEALLOCATE ROW statement.

If you deallocate nonexistent collection variables or variables that are not collection variables, you receive errors. After you deallocate a collection variable, you can use the ALLOCATE COLLECTION to reallocate resources and you can then reuse a collection variable.

This example shows how to deallocate resources with the DEALLOCATE COLLECTION statement for the untyped collection variable, a_set:
EXEC SQL BEGIN DECLARE SECTION;
   client collection a_set;
EXEC SQL END DECLARE SECTION;
. . .
EXEC SQL allocate collection :a_set;
. . .
EXEC SQL deallocate collection :a_set;

For a related example, see the related concept, Inserting into a Collection Cursor.