Restrictions on collection cursors

When you use a Collection cursor to fetch individual elements from a collection variable, the FOREACH statement has the following restrictions:
  • It cannot contain the WITH HOLD keywords.
  • It must contain a restricted SELECT statement in the FOREACH loop.
In addition, the SELECT statement that you associate with the Collection cursor has the following restrictions:
  • Its general structure is SELECT...; INTO ...; FROM TABLE. The statement selects one element at a time from a collection variable specified after the TABLE keyword into another variable called an element variable.
  • It cannot contain an expression in the Projection list.
  • It cannot include the following clauses or options: WHERE, GROUP BY, ORDER BY, HAVING, INTO TEMP, and WITH REOPTIMIZATION.
  • The data type of the element variable must be the same as the element type of the collection.
  • The data type of the element variable can be any opaque, distinct, or collection data type, or any built-in data type except BIGSERIAL, BLOB, BYTE, CLOB, SERIAL, SERIAL8, or TEXT.
  • If the collection contains opaque, distinct, built-in, or collection types, the projection list must be an asterisk ( * ) symbol.
  • If the collection contains ROW types, the projection list can be a list of one or more field names.