Select from a row variable
The SELECT statement and the collection-derived table clause allow you to select a particular field or group of fields in the row variable.
The INTO clause identifies the host variables that hold the field values selected from the row-type variable. The data type of the host variable in the INTO clause must be compatible with the field type.
- No expressions are allowed in the select list.
- The select list must be an asterisk (*) if the row contains elements of opaque, distinct, or built-in data types.
- Column names in the select list must be simple column names.
These columns cannot use the database@server:table.column syntax.
- The select list cannot use dot notation to access fields of the row.
- The following SELECT clauses are not allowed: GROUP BY, HAVING, INTO TEMP, ORDER BY, and WHERE.
- The FROM clause has no provisions to do a join.
- Row-type columns cannot be specified in a comparison condition in a WHERE clause.
EXEC SQL select row_col into :nested_row from tab_row
EXEC SQL select b.x, b.y /* invalid syntax */
into :x_var, :y_var from table(:nested_row);
The application can use dot notation to access fields of a nested row when a SELECT statement accesses a database column. For more information, see Select fields of a row column.