Home » PL/SQL Tutorial » PL/SQL VARRAY

PL/SQL VARRAY

Summary: in this tutorial, you will learn about the PL/SQL VARRAY and how to manipulate elements of a VARRAY effectively.

Introduction to PL/SQL VARRAY #

VARRAY stands for the variable-sized array.

A VARRAY is single-dimensional collections of elements with the same data type. Unlike an associative array and nested table, a VARRAY always has a fixed number of elements(bounded) and never has gaps between the elements (not sparse).

Declare a VARRAY type #

To declare a VARRAY type, you use this syntax:

TYPE type_name IS VARRAY(max_elements)
    OF element_type [NOT NULL];
Code language: SQL (Structured Query Language) (sql)

In this declaration:

  • type_name is the type of the VARRAY.
  • max_elements is the maximum number of elements allowed in the VARRAY.
  • NOT NULL specifies that the element of the VARRAY of that type cannot have NULL elements. Note that a VARRAY variable can be null, or uninitialized.
  • element_type is the type of elements of the VARRAY type’s variable.

To create a VARRAY type which is accessible globally in the database, not just in your PL/SQL code, you use the following syntax:

CREATE [OR REPLACE ] TYPE type_name AS | IS
    VARRAY(max_elements) OF element_type [NOT NULL];
Code language: SQL (Structured Query Language) (sql)

In this declaration, the OR REPLACE modifies existing type while keeping all existing grants of privileges.

Declare and initialize VARRAY variables #

Once you created your own VARRAY type, you can declare a VARRAY instance of that type by referencing the VARRAY type. The basic syntax for VARRAY declaration is:

varray_name type_name [:= type_name(...)];Code language: SQL (Structured Query Language) (sql)

In this syntax:

  • The varray_name is the name of the VARRAY.
  • The type_name is the VARRAY type.
  • The type_name(...) is the constructor of the VARRAY type, which accepts a comma-separated list of elements as arguments. It has the same name as the VARRAY type.

Note that before using a VARRAY variable, you must initialize it. Otherwise, you will receive the following error:

ORA-06531: reference to uninitialized collection
Code language: SQL (Structured Query Language) (sql)

To initialize a VARRAY variable to an empty collection (zero elements), you use the following syntax:

varray_name type_name := type_name();Code language: SQL (Structured Query Language) (sql)

If you want to specify elements for the VARRAY variable while initializing it, you can use this syntax:

varray_name type_name := type_name(element1, element2, ...);Code language: SQL (Structured Query Language) (sql)

Accessing array elements #

To access an array element you use the following syntax:

varray_name(n);Code language: SQL (Structured Query Language) (sql)

n is the index of the element, which begins with 1 and ends with the max_elements the maximum number of elements defined in the VARRAY type.

If n is not in the range (1, max_elements), PL/SQL raises the SUBSCRIPT_BEYOND_COUNT error.

PL/SQL VARRAY examples #

Let’s take some examples of using VARRAY variables.

1) Simple PL/SQL VARRAY example #

The following block illustrates a simple example of using VARRAY variables:

DECLARE
    TYPE t_name_type IS VARRAY(2) 
        OF VARCHAR2(20) NOT NULL;
    t_names t_name_type  := t_name_type('John','Jane');
    t_enames t_name_type := t_name_type();
BEGIN
    -- initialize to an empty array
    dbms_output.put_line("The number of elements in t_enames " || t_enames.COUNT);
    
    -- initialize to an array of a elements 
    dbms_output.put_line("The number of elements in t_names " || t_names.COUNT);
END;
/
Code language: SQL (Structured Query Language) (sql)

In this example:

First, declare a VARRAY of VARCHAR(2) with two elements:

TYPE t_name_type IS 
    VARRAY(2) OF VARCHAR2(20) NOT NULL;
Code language: SQL (Structured Query Language) (sql)

Next, declare a VARRAY variable and initialize it to a VARRAY of two elements:

t_names t_name_type  := t_name_type('John','Jane');
Code language: SQL (Structured Query Language) (sql)

Then, declare another VARRAY variable and initialize it to an empty array:

t_enames t_name_type := t_name_type();
Code language: SQL (Structured Query Language) (sql)

After that, use the COUNT method to get the number of elements in the VARRAY t_enames and display it.

dbms_output.put_line("The number of elements in t_enames " || t_enames.COUNT);
Code language: SQL (Structured Query Language) (sql)

Finally, use the same COUNT method to get the number of elements in the VARRAY t_names and print it out.

dbms_output.put_line("The number of elements in t_names " || t_names.COUNT);
Code language: SQL (Structured Query Language) (sql)

Note that you can assign a VARRAY to another using the following syntax:

varray_name := another_varray_name;
Code language: SQL (Structured Query Language) (sql)

For example:

t_enames := t_names;
Code language: SQL (Structured Query Language) (sql)

PL/SQL copies all members of t_names to t_enames.

2) PL/SQL VARRAY of records example #

See the following example:

DECLARE
    TYPE r_customer_type IS RECORD(
        customer_name customers.NAME%TYPE,
        credit_limit customers.credit_limit%TYPE
    ); 
    
    TYPE t_customer_type IS VARRAY(2) 
        OF r_customer_type;

    t_customers t_customer_type := t_customer_type();
BEGIN
    t_customers.EXTEND;
    t_customers(t_customers.LAST).customer_name := 'ABC Corp';
    t_customers(t_customers.LAST).credit_limit  := 10000;
    
    t_customers.EXTEND;
    t_customers(t_customers.LAST).customer_name := 'XYZ Inc';
    t_customers(t_customers.LAST).credit_limit  := 20000;
    
    dbms_output.put_line('The number of customers is ' || t_customers.COUNT);
END;
/
Code language: SQL (Structured Query Language) (sql)

First, define a record type that includes two fields customer name and credit limit.

TYPE r_customer_type IS RECORD(
    customer_name customers.name%TYPE,
    credit_limit customers.credit_limit%TYPE
); 
Code language: SQL (Structured Query Language) (sql)

Next, declare a VARRAY type of the record r_customer_type with the size of two:

TYPE t_customer_type IS VARRAY(2) 
    OF r_customer_type;
Code language: SQL (Structured Query Language) (sql)

Then, declare a VARRAY variable of the VARRAY type t_customer_type:

t_customers t_customer_type := t_customer_type();
Code language: SQL (Structured Query Language) (sql)

After that, use the EXTEND method to add an instance to t_customers and the LAST method to append an element at the end of the VARRAY t_customers

t_customers.EXTEND;
t_customers(t_customers.LAST).customer_name := 'ABC Corp';
t_customers(t_customers.LAST).credit_limit  := 10000;
    
t_customers.EXTEND;
t_customers(t_customers.LAST).customer_name := 'XYZ Inc';
t_customers(t_customers.LAST).credit_limit  := 20000;
Code language: SQL (Structured Query Language) (sql)

Finally, use the COUNT method to get the number of elements in the array:

dbms_output.put_line('The number of customers is ' || t_customers.COUNT);
Code language: SQL (Structured Query Language) (sql)

Here is the output of the block:

The number of customers is 2
Code language: SQL (Structured Query Language) (sql)

3) Adding elements to VARRAY from a cursor example #

The following example uses a cursor to retrieve five customers who have the highest credits from the customers table and add data to a VARRAY:

customers table
DECLARE
    TYPE r_customer_type IS RECORD(
        customer_name customers.name%TYPE,
        credit_limit customers.credit_limit%TYPE
    ); 

    TYPE t_customer_type IS VARRAY(5) 
        OF r_customer_type;
    
    t_customers t_customer_type := t_customer_type();

    CURSOR c_customer IS 
        SELECT NAME, credit_limit 
        FROM customers 
        ORDER BY credit_limit DESC 
        FETCH FIRST 5 ROWS ONLY;
BEGIN
    -- fetch data from a cursor
    FOR r_customer IN c_customer LOOP
        t_customers.EXTEND;
        t_customers(t_customers.LAST).customer_name := r_customer.name;
        t_customers(t_customers.LAST).credit_limit  := r_customer.credit_limit;
    END LOOP;

    -- show all customers
    FOR l_index IN t_customers .FIRST..t_customers.LAST 
    LOOP
        dbms_output.put_line(
            'The customer ' ||
            t_customers(l_index).customer_name ||
            ' has a credit of ' ||
            t_customers(l_index).credit_limit
        );
    END LOOP;

END;
/
Code language: SQL (Structured Query Language) (sql)

In this example:

First, declare a record type, a VARRAY type of the record with 5 elements, and a VARRAY variable of that VARRAY type:

TYPE r_customer_type IS RECORD(
    customer_name customers.name%TYPE,
    credit_limit customers.credit_limit%TYPE
); 

TYPE t_customer_type IS VARRAY(5) 
    OF r_customer_type;
    
t_customers t_customer_type := t_customer_type();
Code language: SQL (Structured Query Language) (sql)

Second, declare a cursor that retrieves 5 customers with the highest credits:

CURSOR c_customer IS 
    SELECT name, credit_limit 
    FROM customers 
    ORDER BY credit_limit DESC 
    FETCH FIRST 5 ROWS ONLY;
Code language: SQL (Structured Query Language) (sql)

Third, process the cursor and append each element to the VARRAY t_customers:

FOR r_customer IN c_customer LOOP
    t_customers.EXTEND;
    t_customers(t_customers.LAST).customer_name := r_customer.name;
    t_customers(t_customers.LAST).credit_limit  := r_customer.credit_limit;
END LOOP;Code language: SQL (Structured Query Language) (sql)

Finally, iterate over the elements of the VARRAY t_customers and print out the customer name and credit:

FOR l_index IN t_customers .FIRST..t_customers.LAST 
    LOOP
        dbms_output.put_line(
            'The customer ' ||
            t_customers(l_index).customer_name ||
            ' has a credit of ' ||
            t_customers(l_index).credit_limit
        );
    END LOOP;Code language: SQL (Structured Query Language) (sql)

Here is the output:

The customer General Mills has a credit of 179916.92
The customer NextEra Energy has a credit of 141953.76
The customer Southern has a credit of 127665.21
The customer Jabil Circuit has a credit of 113340.75
The customer Progressive has a credit of 94989.78
Code language: SQL (Structured Query Language) (sql)

Delete elements #

To delete all elements of a VARRAY, you use the DELETE method:

varray_name.DELETE;Code language: SQL (Structured Query Language) (sql)

To remove one element from the end of a VARRAY, you use the TRIM method:

varray_name.TRIM;Code language: SQL (Structured Query Language) (sql)

To remove n elements from the end of a VARRAY, you use the TRIM(n) method:

varray_name.TRIM(n)Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned about PL/SQL VARRAY and how to manipulate elements of a VARRAY effectively.

Was this tutorial helpful?
PL/SQL Nested Tables
Previously
PL/SQL Nested Tables
Up Next
Oracle Aggregate Functions
Oracle Aggregate Functions