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