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:

Syntax for Querying Varray

Syntax for Varray Query 
SELECT <varray name>
FROM table_name
WHERE <condition for selection>;

Location 1 The SELECT clause containing a varray column
Location 2 The FROM clause containing the table name for the object table
Location 3 The WHERE clause to select specific records

Three Types of Collections

There are three types of collections in the Oracle Database 11g family of products. They are the
  1. varray,
  2. nested table, and
  3. associative array datatypes.
Collections are powerful structures because they enable you to develop programs that manage large sets of data in memory.
You can build collections of any SQL or PL/SQL datatype. Collections of SQL datatypes work in both SQL and PL/SQL environments but collections of PL/SQL datatypes do not. They only work in PL/SQL. There is also some coverage of using collections as database columns. It covers these topics:
  1. Collection types
    1. Varrays
    2. Nested tables
    3. Associative arrays
  2. Collection set operators
  3. Collection API
Collections are programming structures that hold sets of like things. Collections fall into two categories: 1) arrays and 2) lists. Arrays typically have a physical size allocated when you define them, while lists have no physical limit imposed. Naturally, the memory available for processing in the SGA curtails the maximum size of some very large lists. These lists are often indexed by a series of sequential numbers that start with 0 or 1 and increase one value at a time. Using sequential numeric index values ensures that you can use the index to traverse a complete list by incrementing or decrementing one at a time in a loop. Alternatively, lists can be indexed by non-sequential numbers or unique strings. Lists are called associative arrays when they can be indexed by non-sequential numbers or unique strings. Figure 4-4 illustrates a collection of strings as an inverted tree, which represents a singledimensional collection. It uses a sequentially numbered index and would work with any SQL datatype or PL/SQL scalar or user-defined object type. The caveat on PL/SQL datatypes is that they can only be used in the context of PL/SQL blocks.

FIGURE 4-4: An inverted tree diagram of a single-dimensional SQL datatype collection
FIGURE 4-4: An inverted tree diagram of a single-dimensional SQL datatype collection



The following diagram shows an example:

Querying a varray Example in Oracle

Querying a varray example 
SELECT phone_list
FROM customer_obj_table
WHERE cust_id = 26;

Location 1 The SELECT clause with phone_list (a varray column)
Location 2 The FROM clause containing customer_obj_table
Location 3 The WHERE clause to select specific records

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