Selecting ROW Fields

You can select a specific field of a named or unnamed ROW type column with row.field notation, using a period ( . ) as a separator between the row and field names. For example, suppose you have the following table structure:

CREATE ROW TYPE one (a INTEGER, b FLOAT);
CREATE ROW TYPE two (c one, d CHAR(10));
CREATE ROW TYPE three (e CHAR(10), f two);

CREATE TABLE new_tab OF TYPE two;
CREATE TABLE three_tab OF TYPE three;

The following examples show expressions that are valid in the select list:

SELECT t.c FROM new_tab t;
SELECT f.c.a FROM three_tab;
SELECT f.d FROM three_tab;

For example, if the my_tab table has a ROW-type column named rowcol that contains four fields, the following SELECT statement retrieves all four fields of the rowcol column:

SELECT rowcol.* FROM my_tab;

You can also retrieve all fields from a row-type column by specifying only the column name. This example has the same effect as the previous query:

SELECT rowcol FROM my_tab;