RelationalDBDesignRelationalDBDesign


PL/SQL   «Prev 

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 MouseOver.