Select from a collection variable

The SELECT statement with the collection-derived table clause allows you to select elements from a collection variable.

The collection-derived table clause identifies the collection variable from which to select the elements. The SELECT statement on a client collection variable (one that has the collection-derived table clause) has the following restrictions:
  • The select list of the SELECT cannot contain expressions.
  • The select list must be an asterisk (*).
  • Column names in the select list must be simple column names.

    These columns cannot use the database@server:table.column syntax.

  • The following SELECT clauses and options are not allowed: GROUP BY, HAVING, INTO TEMP, ORDER BY, WHERE, WITH REOPTIMIZATION.
  • The FROM clause has no provisions to do a join.
The SELECT statement and the collection-derived table clause allow you to perform the following operations on a collection variable:
  • Select one element from the collection

    Use the SELECT statement with the collection-derived table clause.

  • Select one row element from the collection.

    Use the SELECT statement with the collection-derived table clause and a row variable.

  • Select one or more elements into the collection

    Associate the SELECT statement and the collection-derived table clause with a cursor to declare a select cursor for the collection variable.