Complete Guide to Creating In-Memory Array Variables in Oracle PL/SQL

Overview of PL/SQL In-Memory Arrays

In Oracle PL/SQL programming, developers frequently need to handle data collections. Unlike some programming languages, PL/SQL provides specialized collection types for creating pure in-memory arrays that are completely independent of database tables, allowing efficient data storage and manipulation during program execution.

VARRAY: Fixed-Size Arrays

VARRAY (Variable-Size Array) is the most commonly used fixed-size array type in PL/SQL. It allows developers to define ordered collections of elements with predefined maximum capacity. All VARRAY elements are stored contiguously in memory, providing high access efficiency.

VARRAY Type Declaration

The basic syntax for declaring VARRAY types within a PL/SQL block is:

TYPE array_type_name IS VARRAY(maximum_size) OF element_data_type;

Where:

• array_type_name: User-defined array type name
• maximum_size: Maximum capacity of the array
• element_data_type: Data type of array elements

VARRAY Initialization and Usage

The following example demonstrates how to declare, initialize, and use VARRAY:

DECLARE
   TYPE name_array IS VARRAY(3) OF VARCHAR2(10);
   names name_array := name_array('Matt', 'Joanne', 'Robert');
BEGIN
   FOR i IN 1..names.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(names(i));
   END LOOP;
END;

In this example:

• The constructor name_array() is used to directly initialize the array
• Array indexing starts from 1, not 0
• The COUNT attribute retrieves the current number of elements
• Elements are accessed via indexing: names(i)

Dynamic Array Operations

For scenarios requiring dynamic array construction, the EXTEND method can be used to gradually add elements:

DECLARE
   TYPE dynamic_array IS VARRAY(5) OF VARCHAR2(10);
   arr dynamic_array := dynamic_array();
BEGIN
   FOR i IN 1..3 LOOP
      arr.EXTEND();
      arr(i) := 'Element ' || TO_CHAR(i);
   END LOOP;
   
   FOR i IN 1..arr.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(arr(i));
   END LOOP;
END;

Key considerations:

• The array must first be initialized using the constructor
• The EXTEND() method must be called before assignment
• The actual size of VARRAY cannot exceed the declared maximum capacity

TABLE Type: Unbounded Arrays

In addition to VARRAY, PL/SQL provides the TABLE type for creating unbounded arrays (dynamically sized arrays):

DECLARE
   TYPE unbounded_array IS TABLE OF VARCHAR2(10);
   names unbounded_array := unbounded_array();
BEGIN
   names.EXTEND(3);
   names(1) := 'Tim';
   names(2) := 'Daisy';
   names(3) := 'Mike';
   
   FOR i IN names.FIRST..names.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(names(i));
   END LOOP;
END;

Predefined Collection Types

Oracle also provides predefined collection types, such as DBMS_SQL.VARCHAR2_TABLE, which can be used directly without custom type definitions:

DECLARE
   name_list DBMS_SQL.VARCHAR2_TABLE;
BEGIN
   name_list(1) := 'Marsha';
   name_list(2) := 'John';
   name_list(3) := 'Lisa';
   
   FOR i IN name_list.FIRST..name_list.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(name_list(i));
   END LOOP;
END;

Advanced Applications of Associative Arrays

Associative arrays (formerly called PL/SQL tables) provide more flexible indexing mechanisms, allowing any integer to be used as an index:

DECLARE
   TYPE employee_table IS TABLE OF employees%ROWTYPE
        INDEX BY PLS_INTEGER;
   emp_data employee_table;
BEGIN
   SELECT *
   BULK COLLECT INTO emp_data
   FROM employees
   WHERE department_id = 10;
   
   FOR i IN emp_data.FIRST..emp_data.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(emp_data(i).employee_name);
   END LOOP;
END;

Performance Considerations and Best Practices

When selecting array types, consider the following factors:

• VARRAY: Suitable for ordered collections with known maximum size, contiguous memory, fast access
• TABLE: Suitable for dynamically sized collections, providing greater flexibility
• Associative Arrays: Suitable for scenarios requiring custom indexing or sparse arrays

All PL/SQL collection types are pure memory structures, completely independent of database tables, ensuring efficient data processing performance.