If a column defined within a table is a nested table, and you would like to insert, update, delete, or select from the contents of such a column,
you cannot do so with any of the SQL statements available within Oracle7.
Instead, you must use a
keyword, THE
.
This tells Oracle the row from the nested table you would like to alter. Using the
THE
function is called
flattening[1] a nested table. A flattened table is a combination of two tables that look like a single table. Querying a flattened table implies querying a parent-child relationship within the same query using object tables.
Oracle8i provides four collection functions that make the database tables act like collections and vice versa.
To query, update, insert, or to delete records from the nested table, it must be queried as if it were a single table. This is achieved by the usage of "THE" or "TABLE" functions .
THE
CAST
MULTISET
TABLE
The THE
function
The
THE
function maps a single column value within a single row to a virtual database table. In other words, the
THE
function retrieves the value of each element of the nested table within the outer table; thus, when inserting, updating, deleting, or selecting the attributes of the nested table, you cannot use dot notation; you must use
THE
function.
The following Tooltip explains the syntax for flattening a nested table: