Oracle VARRAY Examples Oracle Tips by Burleson Consulting Don Burleson

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 created anyway.

rem There is a fixed number of FTX codes and it is small
rem so use a VARRAY
rem
CREATE OR REPLACE TYPE ftx_t (
   ftx_code          CHAR(8) ,
   ftx_code_desc     VARCHAR2(32),
primary_ftx_code_ind CHAR(1)
);
rem
rem ftx_v is a VARRAY of 6 elements
rem
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 employee_info table.

insert into
   employee_info
values(1,name_t('Michael','Burleson','R'), <--- Note name_t constructor
dependent_list( <--- Note use of dependent_list constructor
dependent_t('Wife',name_t('Janet','Burleson','K'),'39'),
dependent_t('Daughter',name_t('Jen','Burleson','C'),23),
dependent_t('Daughter',name_t('Andy','Burleson','E'),19))
6* );

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 like so:

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 a type.

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 nested table.
  
• 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 a
  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 atomically null.
• NO_DATA_FOUND -- Subscript points to a null instance of the collection.
• 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);
END LOOP

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 to FIRST.

Once we have processed the first value we reset out counter to the appropriate NEXT value. Once NEXT evaluates to NULL we exit the loop.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle 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 buy it for 30% off directly from the publisher.

---