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 diagram 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 WHERE condition for the table_alias
SELELCT VALUE (table_alias) FROM THE 
(SELECT <varray_name> from <object_table_name>
WHERE <condition for selection>) table_alias;

Flattening a Nested Table Syntax

Flattening a nested table syntax 
SELECT <expression-list>
  FROM THE (SELECT <outer column name> 
    FROM <outer table>
    WHERE <condition on outer table>)
  WHERE <condition on inner table>;

Location 1 The SELECT clause for selecting attributes (that is, columns) from the nested table
Location 2 The FROM THE clause for specifying details of the outer table
Location 3 The SELECT clause for selecting the nested table column from the outer table and specifying the particular row within the WHERE clause
Location 4 The FROM clause to select the outer table
Location 5 The WHERE clause to select a particular row from the outer table
Location 6 WHERE <condition on inner table>, The WHERE clause to select a particular row from the inner table

Querying Flattened varray Example

SELECT VALUE (a) FROM THE (SELECT phone_list FROM customer_obj_table WHERE cust_id = 29) 
SELECT VALUE (a) FROM THE 
  (SELECT phone_list FROM customer_obj_table
	WHERE cust_id = 29)  a;

Location 1 The first SELECT clause
Location 2 The second SELECT clause
Location 3 The second SELECT clause
Nested tables support a great variety of queries. A nested table is a column within a table. To support queries of the columns and rows of a nested table, Oracle provides a special keyword, THE (which has been deprecated for some time). To see how the THE keyword is used, first consider the nested table by itself. If it were a normal column of a relational table, you would be able to query it via a normal select command.


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

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