Oracle VARRAY Examples
Oracle Tips by Burleson Consulting
Create of a VARRAY Type
Another special type is the VARRAY or varying array type. This type
should be used when the number of instances to be stored is small.
This type is stored inline with the other table data. In early
releases (up to 8.0.3) the varray could take up to 25 times the amount
of storage as a nested table for the same values. Unless you are on a
newer release where this has been corrected I suggest use of the
nested table instead of a VARRAY. However, let's examine how they are
rem There is a fixed number of FTX codes and it is small
rem so use a VARRAY
CREATE OR REPLACE TYPE ftx_t (
ftx_code CHAR(8) ,
rem ftx_v is a VARRAY of 6 elements
CREATE OR REPLACE TYPE ftx_v AS VARRAY(6) OF ftx_t;
Notice that the number of instances of the VARRAY is set at six(6) in
this example. This required 676 bytes of RAW inline storage. You have
no control over storage and can not index or constrain varray values.
Populating VARRAY Types
As was stated in other sections the creation of a type automatically
creates a method (constructor) to populate the type. To call the
method the original type name is used. The constructor is never called
implicitly but must be called explicitly. The constructor can be used
anywhere a function call is used. Here is an example insert into the
<--- Note name_t constructor
dependent_list( <--- Note use of dependent_list constructor
1 row created
Notice that the format for using the constructor methods walks down
the type tree. In this case the type tree for the dependent type looks
dependents <---- Attribute in table
dependent_list <----- Nested Table Type
dependent_t <----- Base Type
“Dependents” is the attribute so it isn't used in the type tree.
Therefore, we start at the dependent_list type as the outermost method
and end at dependent_t the basic type constructor method. Since the
type is for a nested table, we specify the dependent_t method multiple
times for each insert into the nested table. Notice that we also use a
constructor method, name_t, inside the innermost type constructor to
perform the insert into the dependents name attribute which is itself
Population of a varray type is identical to population of a nested
table type with the exception that you can only insert up to the
maximum specified count of the varray.
Use of Other Built-in Methods for Collections
In addition to the constructor type Oracle also provides collection
methods for use with VARRAYS and nested tables. These methods cannot
be used outside of PL/SQL. Collection methods cannot be used in DML
but only in procedural statements.
- EXISTS -- Used to determine if a specific element in a collection
exists. EXISTS is used with nested tables.
- COUNT -- Returns the number of elements that a collection currently
contains not including null values. For varrays count is equal to
LAST. For nested tables COUNT and LAST may be different due to deleted
values in interstitial data sites in the nested table.
- LIMIT -- Used for VARRAYS to determine the maximum number of values
allowed. If LIMIT is used on a nested table it will return a null.
- FIRST and LAST -- Return the smallest and largest index numbers for
the collection referenced. Naturally, they return null if the
collection is empty. For
VARRAYS FIRST always returns 1, for nested tables FIRST returns the
value of the first filled spot for that entry. LAST returns the last
filled instance of a VARRAY and a nested table. For a VARRAY COUNT
will always equal LAST. For a nested table they can be different but
LAST should always be greater than COUNT if they are different for a
- PRIOR and NEXT -- Return the prior or next value based on the input
value for the collection index. PRIOR and NEXT ignore deleted
instances in a collection.
- EXTEND -- Appends instances to a collection. EXTEND has three forms,
EXTEND, which adds one null instance, EXTEND(n) which adds "n" null
instances and EXTEND(n,m) which appends N copies of instance "m" to
the collection. For not null specified collections forms one and two
cannot be used.
- TRIM -- Trim removes instances from a collection. TRIM used with no
arguments removes the last instance, TRIM(n) removes "n" instances
from the collection.
- DELETE -- DELETE removes specified items from a nested table or all of
VARRAY. DELETE specified with no arguments removes all instances of a
collection. For nested tables only DELETE(n) removes the nth instance
and DELETE(n,m) deletes the nth through the mth instances of the
nested table that relate to the specified master record.
Remember when using these methods that a VARRAY is a dense type, that
its values start at one and go to the last filled value as far as the
index is concerned with no breaks and no null values allowed. A nested
table is allowed to have deleted values (i.e. it can be "sparse").
Therefore if you attempt to use a method that is inappropriate such as
DELETE with an argument against a VARRAY you will receive an error.
Collection methods can raise the following exceptions:
- COLLECTION_IS_NULL -- Caused when the collection referenced is
- NO_DATA_FOUND -- Subscript points to a null instance of the
- SUBSCRIPT_BEYOND_COUNT -- The specified subscript is beyond the number
of instances in the collection.
- SUBSCRIPT_OUTSIDE_LIMIT -- The specified subscript is outside the
legal range (usually received from VARRAY references)
- VALUE_ERROR -- Subscript is null or is not an integer.
These collection methods can be used in a variety of ways. In the case
of a nested table where some of the values are null, selection using a
standard cursor could result in an exception. Use the FIRST and NEXT
collection methods to transverse these null values.
j := dependents.FIRST;
WHILE j IS NOT NULL LOOP
… process dependents(j)…
j := dependents.NEXT(j);
So what does this code fragment do? First, we use the FIRST collection
method to get the index integer of the first valid record for the
dependents nested table. Remember that a nested table is a sparse
construct so the first valid value may not be one. Next, we begin loop
processing of values assuming we didn’t get a null value on our call
Once we have processed the first value we reset out counter
to the appropriate NEXT value. Once NEXT evaluates to NULL we exit the
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.