Delete all elements

You cannot include a WHERE clause on a DELETE statement with a collection-derived table clause. Therefore, a DELETE statement on a collection variable deletes all elements from the collection. No update cursor is required to delete all elements of a collection.

For example, the following DELETE removes all elements in the a_list collection variable:
EXEC SQL BEGIN DECLARE SECTION;
   client collection list(smallint not null) a_list;
EXEC SQL END DECLARE SECTION;
;

EXEC SQL delete from table(:a_list);