PL/SQL   «Prev  Next»

Lesson 5 A better way to query a varray
ObjectiveWrite 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.

Select Expression List
  1. The first SELECT clause, containing a value of the table alias
  2. The second SELECT clause, containing the varray name within the object table to select specific records
  3. 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;

Flattening Nested Table Syntax
Syntax Flat varray
  1. The first SELECT clause
  2. The second SELECT clause
  3. The second SELECT clause
SELECT VALUE(a) FROM THE 
(SELECT phone_list FROM customer_obj_table
WHERE cust_id=29) a;

Querying Flattened Varray Example
In the next lesson, we will describe the reasons for using PL/SQL to query varrays and nested tables.

Preferrable Method to Query Varray - Exercise

Click the Exercise link below to try your hand at writing queries to select varrays.
Preferrable Method to Query Varray - Exercise