Select with a Collection-Derived Table

The diagram for DECLARE statement refers to this section.

To declare a Select cursor for a collection variable, include the Collection- Derived Table segment with the SELECT statement that you associate with the Collection cursor. A Select cursor allows you to select one or more elements from the collection variable. (For a description of SELECT syntax and usage, see SELECT statement.)

When you declare a Select cursor for a collection variable, the DECLARE statement has the following restrictions:

It cannot include the FOR READ ONLY keywords as cursor mode.
The Select cursor is an update cursor.
It cannot include the SCROLL or WITH HOLD keywords.
The Select cursor must be a sequential cursor.

In addition, the SELECT statement that you associate with the collection cursor has the following restrictions:

It cannot include the following clauses or options: WHERE, GROUP BY, ORDER BY, HAVING, INTO TEMP, and WITH REOPTIMIZATION.
It cannot contain expressions in the projection list.
If the collection contains elements of opaque, distinct, built-in, or other collection data types, the projection list must be an asterisk ( * ).
Column names in the projection list must be simple column names, without qualifiers.
These columns cannot use the following syntax:
```
database@server:table.column --INVALID SYNTAX
```
It must specify the name of the collection variable in the FROM clause.
You cannot specify an input parameter (the question-mark ( ? ) symbol) for the collection variable. Likewise, you cannot use the virtual table format of the Collection-Derived Table segment.