12.5 Collection Unnesting

Even if your developer community is comfortable manipulating collections within your database, it is often unrealistic to expect the various tools and applications accessing your data (data load and extraction utilities, reporting and ad-hoc query tools, etc.) to correctly handle them. Using a technique called collection unnesting, you can present the contents of the collection as if it were rows of an ordinary table. For example, using the TABLE expression, you can write a query which unnests the order_items nested table from the cust_order_c table, as in:

SELECT co.order_nbr, co.cust_nbr, co.order_dt, li.part_nbr, li.quantity

FROM cust_order_c co, 

  TABLE(co.order_items) li;



ORDER_NBR   CUST_NBR ORDER_DT  PART_NBR               QUANTITY

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

      1000       9568 21-MAR-01 A675-015                     25

      1000       9568 21-MAR-01 GX5-2786-A2                   1

      1000       9568 21-MAR-01 X378-9JT-2                    3

Note that the two data sets do not need to be explicitly joined, since the collection members are already associated with a row in the cust_order_c table.

To make this unnested data set available to your users, you can wrap the previous query in a view:

CREATE VIEW cust_order_line_items AS

SELECT co.order_nbr, co.cust_nbr, co.order_dt, li.part_nbr, li.quantity

FROM cust_order_c co, 

  TABLE(co.order_items) li;

Your users can now interact with the nested table via the view using standard SQL, as in the following:

SELECT *

FROM cust_order_line_items

WHERE part_nbr like 'X%';



 ORDER_NBR   CUST_NBR ORDER_DT  PART_NBR               QUANTITY

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

      1000       9568 21-MAR-01 X378-9JT-2                    3