12.4 Querying Collections

Now that you know how to get data into a collection, you need a way to get it out. Oracle provides a special TABLE expression just for this purpose.

Prior to release 8i, the TABLE expression was called THE. Only the TABLE expression is used here.

The TABLE expression can be used in the FROM, WHERE, and HAVING clauses of a query to allow a nested table or varray column to be referenced as if it were a separate table. The following query extracts the resupply dates (from the restocks column) that were added previously to the part_c table:

SELECT * 

FROM TABLE(SELECT restocks 

  FROM part_c

  WHERE part_nbr = 'GX5-2786-A2');



COLUMN_VALUE

-----------

03-SEP-99

22-APR-00

21-MAR-01

To better illustrate the function of the TABLE expression, the next query retrieves the restocks varray directly from the part_c table:

SELECT restocks 

FROM part_c

WHERE part_nbr = 'GX5-2786-A2';



RESTOCKS

------------------------------------------------------

RESUPPLY_DATES('03-SEP-99', '22-APR-00', '21-MAR-01')

As you can see, the result set consists of a single row containing an array of dates, whereas the previous query unnests the varray so that each element is represented as a row with a single column.

Since the varray contains a built-in data type rather than an object type, it is necessary to give the varray name so that it may be explicitly referenced in SQL statements. Oracle assigns the varray's contents a default alias of column_value for this purpose. The next example makes use of the column_value alias.

Let's say that you wanted to find all parts resupplied on a particular date. Using the TABLE expression, you can perform a correlated subquery against the restocks varray to see if the desired date is found in the set:

SELECT p1.part_nbr, p1.name

FROM part_c p1

WHERE TO_DATE('03-SEP-1999','DD-MON-YYYY') IN

 (SELECT column_value FROM TABLE(SELECT restocks FROM part_c p2 

  WHERE p2.part_nbr = p1.part_nbr));



PART_NBR             NAME

-------------------- -------------------------------

GX5-2786-A2          Spacely Sprocket