Write a query that flattens the varray into a nested table.
Query to Flatten Varray into Nested Table in Oracle
In this lesson, we will look at a different approach to querying a varray, that is by flattening it.
This means that you can use the THE and the TABLE functions in the same way you use them to query a nested table.
These two functions query the outer table as if it were a single table, even though it is a varray within a table.
For example, the CUSTOMER_OBJ_TABLE table has a varray that is associated within a relational table.
These two functions can be used within DML statements such as INSERT, UPDATE, or DELETE.
The following MouseOver explains the syntax for querying a flattened varray. For this example, CUSTOMER_OBJ_TABLE is the
outer table and PHONE_ARRAY is the varray. We will query for the varray where cust_id is 29 within the outer table.
The first SELECT clause, containing a value of the table alias
The second SELECT clause, containing the varray name within the object table to select specific records
The second SELECT clause, containing the varray name within the object table to select specific records
SELELCT VALUE (table_alias) FROM THE
(SELECT <varray_name> from <object_table_name>
WHERE <condition for selection>) table_alias;