PL/SQL   «Prev  Next»

Lesson 2 What is flattening?
Objective Define the concept of flattening a nested table.

Flattening Nested Tables in Oracle

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.

These include:
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 .
  1. THE
  2. CAST
  3. MULTISET
  4. 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:

  1. The SELECT clause for selecting attributes (that is, columns) from the nested table
  2. The FROM THE clause for specifying details of the outer table
  3. The SELECT clause for selecting the nested table column from the outer table and specifying the particular row within the WHERE clause
  4. The FROM clause to select the outer table
  5. The WHERE clause to select a particular row from the outer table
  6. The WHERE clause to select a particular row from the inner table
SELECT <expression-list>
FROM THE (SELECT <outer column name>
  FROM <outer table>
  WHERE <condition on outer table>)
WHERE  <condition on inner table>;


Flattening Nested Table Syntax
The following Tooltip uses an example to explain the syntax for flattening a nested table:
  1. The SELECT clause for selecting attributes (that is, columns) from the nested table
  2. The FROM THE clause for specifying details of the outer table
  3. The second SELECT clause for selecting the nested table column (<tt>detail_nest</tt>) from <tt>sale_header</tt> (the outer table)
  4. The FROM clause to select sale_header (the outer table)
  5. The WHERE clause to select a particular row from <tt>sale_header</tt>, the outer table
  6. The WHERE clause to select a particular row from the inner table
SELECT product_id, item_qty, detail_amount
  FROM THE (SELECT detail_nest 
    FROM sale_header
    WHERE sale_id =22)
WHERE detail_amount=78;

Flattening nested Table Example
In the next lesson, you will write a query to flatten a nested table.

[1]Flattening: A relational table which is associated with a nested table consists of two tables, the main relational table and the nested table.