PL/SQL   «Prev  Next»

Lesson 4The easiest way to query a varray
Objective Write a simple array query.

Simple Varray Query in Oracle

To query on all the details of a record within an object table associated with a varray, you must query the varray. To do so, include the varray column name within the SQL statement. In this lesson, we will look at the simplest way to query a varray within an object table. A varray is always associated with an object table.
The simplest way to query a varray is by selecting the varray name within a simple SELECT statement.
This, in turn, will display all the elements within the varray.
The following diagram explains the syntax:
PL/SQL varray syntax
  1. The SELECT clause containing a varray column
  2. The FROM clause containing the table name for the object table
  3. The WHERE clause to select specific records
SELECT <varray_name>
FROM table_name
WHERE <condition for selection>;

Querying Varray Syntax
The following diagram shows an example:
Select Clause
  1. The SELECT clause with phone_list (a varray column)
  2. The FROM clause containing customer_obj_table
  3. The WHERE clause to select specific records
SELECT phone_list
FROM customer_obj_table
WHERE cust_id=26;

Varray example query
In the next lesson, we will examine a more efficient way to query a varray, by using the THE and the TABLE functions.