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.

Oracle Deprecated Feature - THE keyword

The THE function is deprecated effective Oracle 8.1.6. Instead, use the TABLE function. Let's compare both functions. The THE function retrieves the value of each element of the nested table within the outer table. The TABLE function maps a collection to a database table. For example, if you need to determine the rows within a table containing a collection that meets a certain criteria, you need a WHERE clause on the contents of a collection when selecting from a database table. This can be accomplished by using the TABLE function. The query for the SALE_HEADER table, which has a collection, looks like this: 

SELECT sale_id, detail_total, tax_amount, shipping_amount, sale_total 
FROM sale_header sh
WHERE 78 in (SELECT detail_amount FROM TABLE(sh.detail_nest));

This query goes through the SALE_HEADER table and returns all the rows whose list of detail_nest contains at least one instance of 78. As seen in the example above, the TABLE function accepts an alias-qualified collection column as its argument. The TABLE function returns the contents of the collection forced into a virtual database table.
The same details can be queried from the table by using the THE function. You saw an example of this in the flattening a nested table diagram.

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 provided 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 diagram explains the syntax for flattening a nested table:

Flattening a Nested Table Syntax

Flattening a nested table syntax 
SELECT <expression-list>
  FROM THE (SELECT <outer column name> 
    FROM <outer table>
    WHERE <condition on outer table>)
  WHERE <condition on inner table>;

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



Flattening Nested Table in Oracle

The following diagram uses an example to explain the syntax for flattening a nested table:
Flattening a nested table example 
SELECT product_id, item_qty, detail_amount
  FROM THE (SELECT detail_nest 
    FROM sale_header
    WHERE sale_id =22)
WHERE detail_amount=78;

Location 1 The SELECT clause for selecting attributes (that is, columns) from the nested table
Location 2The FROM THE clause for specifying details of the outer table
Location 3 The second SELECT clause to select the nested table column (detail_nest) from sale_header (the outer table)
Location 4 The FROM clause to select sale_header (the outer table)
Location 5 The WHERE clause to select a particular row from sale_header, the outer table
Location 6 The WHERE clause to select a particular row from the inner table

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.