Operate on a row variable

The SELECT, and UPDATE statements allow you to access a row-type column as a whole.

The client application can access individual fields as follows:
  • Use SQL statements and dot notation to directly select, insert, update, or delete fields in row-type columns of the database with SQL statements, as long as these operations involve literal values.

    Unlike collection columns, the SELECT statement can access individual members of row-type columns. Therefore, the client application can directly select or update fields in row-type columns of the database.

  • Use a row host variable to perform operations on the row as a whole or on individual fields.
Restriction: You cannot use dot notation in a SELECT statement to access the fields of a nested row in a row variable.

With a row host variable, you access a row-type column as a collection-derived table. The collection-derived table contains a single row in which each column is a field. A collection-derived table allows you to decompose a row into its fields and then access the fields individually.

The application first performs the operations on the fields through the row host variable. After modifications are complete, the application can save the contents of the row variable into a row-type column of the database.

This section discusses the following topics on how to use a collection-derived table in the application to access a row-type column:
  • How to use the collection-derived table clause in SQL statements to access a row host variable
  • How to initialize a row host variable with a row-type column
  • How to select fields from a row host variable
  • How to update field values in a row host variable