Varray

Varrays were introduced in Oracle8i as a modified format of a nested table. The varray or variable size arrays are bounded and the persistent form of collection whose major operational features resemble nested tables. The varray declaration defines the limit of elements a varray can accommodate. The minimum bound of the index is 1, current bound is the total number of resident elements and maximum bound is the varray size. At any moment, the current bound cannot exceed the maximum bound.

Like nested tables, varrays can be created as database objects and can also be used in PL/SQL. Though the implementation is the same as a nested table, varray follow a different storage orientation than the nested tables. They are stored in line with their parent record as a raw value in the parent table. The inline storage mechanism no more needs a storage clause specification, unique identifier or separate storage table. For some exceptional situations when the varray exceeds 4 K data, Oracle follows the out-of-line storage mechanism and stores varray as an LOB.

The inline storage mechanism of varrays helps Oracle to reduce the number of IOs on the disk. This makes varrays superior and more performance efficient than nested tables.

As a database collection type, varrays can be a valid type for a table column or object type attribute. If declared in a PL/SQL block, varrays are visible only within the block.

The syntax for varrays, when defined as a database collection type, is as follows:

[sourcecode language=”sql”]
CREATE [OR REPLACE] TYPE type_name IS {VARRAY | VARYING ARRAY} (size_ limit) OF element_type
[/sourcecode]

In PL/SQL, varrays can be declared as follows:

[sourcecode language=”sql”]
DECLARE TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];
[/sourcecode]

In the preceding syntax, size_limit represents the maximum count of elements in the array.

If the varray size has to be modified after its creation in the database, follow this ALTER TYPE syntax:

[sourcecode language=”sql”]
ALTER TYPE [varray name] MODIFY LIMIT [new size_limit] [INVALIDATE | CASCADE];
[/sourcecode]

The varray size can only be increased by using the ALTER TYPE… MODIFY statement. Even if the current maximum size has not been utilized, Oracle doesn’t allow the ripping off a varray size. If a user attempts to reduce the varray size, Oracle raises the PLS-00728: the limit of a VARRAY can only be increased and to a maximum 2147483647 exception and invalidates the varray collection type.

The INVALIDATE and CASCADE options signify the invalidation or propagation effect on the dependent objects as a result of the type alteration.

Use the DROP command to drop a varray type from the database:

[sourcecode language=”sql”]
DROP TYPE [varray type name] [FORCE]
[/sourcecode]

Varray in PL/SQL

Similar to the handling of a nested table as PL/SQL construct, varrays also can be declared local to a PL/SQL block. In the following illustrations, observe the scope and visibility of the varray variables.

Similar to nested tables, varrays too follow object orientation. For this reason, varrays require initialization mandatorily before accessing them in the executable section of the PL/SQL block.

[sourcecode language=”sql”]
/*Enable the SERVEROUTPUT to display the results*/
SET SERVEROUTPUT ON
/*Start the PL/SQL block*/
DECLARE
/*Declare a local varray type, define collection variable and
initialize it*/
TYPE V_COLL_DEMO IS VARRAY(4) OF VARCHAR2(100);
L_LOCAL_COLL V_COLL_DEMO := V_COLL_DEMO(‘Oracle 9i’,
‘Oracle 10g’,
‘Oracle 11g’);
BEGIN
/*Use FOR loop to parse the array variable and print the elements*/
FOR I IN 1..L_LOCAL_COLL.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(‘Printing Oracle version:’ ||L_LOCAL_
COLL(I));
END LOOP;
END;
/

Printing Oracle version:Oracle 9i
Printing Oracle version:Oracle 10g
Printing Oracle version:Oracle 11g

PL/SQL procedure successfully completed.
[/sourcecode]

Varray as a database collection type

Let us illustrate the creation of a varray as a database collection type. We will see the SELECT and DMLoperations on varrays:

[sourcecode language=”sql”]
/*Create the nested table in the database*/
SQL> CREATE OR REPLACE TYPE num_varray_t AS VARRAY (5) OF NUMBER;
/

Type created.
[/sourcecode]

Oracle maintains the complete information about the newly created varray types in the dictionary viewsUSER_VARRAYS, USER_COLL_TYPES, and USER_TYPES. Now, we will create a table which has a column of the varray type. Note that it has no NESTED TABLE STORE AS clause as it used in the case of nested tables to specify the name of the storage table.

[sourcecode language=”sql”]
CREATE TABLE tab_use_va_col
(ID NUMBER,
NUM num_varray_t);

Table created.

/*Query the USER_VARRAYS to list varray information*/
SELECT parent_table_column, type_name, return_type, storage_spec
FROM user_varrays
WHERE parent_table_name=’TAB_USE_VA_COL’
/

PARENT_TAB TYPE_NAME RETURN_TYPE STORAGE_SPEC
———- ————— ——————– —————–
NUM NUM_VARRAY_T VALUE DEFAULT
[/sourcecode]