PLSQL

PL/SQL Collections with a Practical Guide

Showkat Hossain

24 Jul 2025 • 5 min read

As a PL/SQL developer, we are always looking for ways to write more efficient, scalable, and maintainable code. One of the most powerful tools in the Oracle Database arsenal for achieving this is the PL/SQL collection. Collections are, simply put, in-memory data structures that let you manage sets of data within your code, similar to arrays or lists in other programming languages.

Why Use PL/SQL Collections?

So, why should we use collections? The primary reason is performance. Every time you execute a SQL statement from within PL/SQL, a "context switch" occurs as the database transitions between the PL/SQL and SQL engines. This switch has an overhead cost.

Collections allow us to bundle data and operations, dramatically reducing these context switches. Instead of fetching or inserting one row at a time in a loop, we can fetch thousands of rows into a collection with a single query (BULK COLLECT) or insert thousands of records from a collection with a single DML statement (FORALL).

Key benefits include:

Improved Performance: Drastically reduce context switching with bulk processing.
Simplified Logic: Easily manage and manipulate sets of related data within your procedures.
Better Code Organization: Group data into structured in-memory tables.

The Three Types of PL/SQL Collections

PL/SQL offers three distinct types of collections, each with its own characteristics and ideal use cases.

Associative Arrays (Index-by Tables)

Associative arrays are sets of key-value pairs. They are the most flexible type, as their index can be either a PLS_INTEGER or a VARCHAR2, and they are "sparse," meaning the index values do not have to be sequential.

Best for: Caching lookup data where you need fast access based on a specific key (like a primary key or an email address).
Code Callout: Let's create an associative array to store lookup value, indexed by lookup code for quick lookup.

    TYPE t_lookup_values IS TABLE OF VARCHAR2(255) INDEX BY PLS_INTEGER;
    TYPE t_lookup_map IS TABLE OF t_lookup_values INDEX BY VARCHAR2(100);
    TYPE t_lookup_desc_map IS TABLE OF VARCHAR2(255) INDEX BY VARCHAR2(100);

    v_lookup_values_map t_lookup_map;
    v_lookup_desc_map   t_lookup_desc_map;

    v_value_idx   PLS_INTEGER;
BEGIN
    v_lookup_desc_map('ACC_TYPE') := 'Account Type';
    v_lookup_desc_map('PAY_TYPE') := 'Payment Type';
    v_lookup_desc_map('PAY_STATUS') := 'Payment Status';

    v_lookup_values_map('ACC_TYPE')(1) := 'Savings';
    v_lookup_values_map('ACC_TYPE')(2) := 'Checking';
    v_lookup_values_map('ACC_TYPE')(3) := 'Loan';

    v_lookup_values_map('PAY_TYPE')(1) := 'Cash';
    v_lookup_values_map('PAY_TYPE')(2) := 'Card';

    v_lookup_values_map('PAY_STATUS')(1) := 'Pending';
    v_lookup_values_map('PAY_STATUS')(2) := 'Completed';

    FOR lookup_code IN (
        SELECT COLUMN_VALUE as code FROM TABLE(SYS.ODCIVARCHAR2LIST('ACC_TYPE', 'PAY_TYPE', 'PAY_STATUS'))
    ) LOOP

        INSERT INTO EXPENSOME_LOOKUPS (LOOKUP_ID, LOOKUP_CODE, LOOKUP_DESCRIPTION
        ) VALUES (EXPENSOME_LOOKUPS_SEQ.NEXTVAL, lookup_code.code, v_lookup_desc_map(lookup_code.code)
        );

        v_value_idx := v_lookup_values_map(lookup_code.code).FIRST;
        WHILE v_value_idx IS NOT NULL LOOP      
            INSERT INTO EXPENSOME_LOOKUP_VALUES (
                LOOKUP_VALUE_ID, LOOKUP_ID, LOOKUP_VALUE_DESCRIPTION
            ) VALUES (
                EXPENSOME_LOOKUP_VAL_SEQ.NEXTVAL,
                EXPENSOME_LOOKUPS_SEQ.CURRVAL,
                v_lookup_values_map(lookup_code.code)(v_value_idx)
            );
            v_value_idx := v_lookup_values_map(lookup_code.code).NEXT(v_value_idx);
        END LOOP;
    END LOOP;
END;
/

Nested Tables

Nested tables are unbounded, meaning they have no predefined size limit. Their index is always a sequential integer starting at 1, and they must be dense (no gaps in the index). A key feature is that they can be stored as a column in a database table.

Best for: Handling a variable number of elements, passing lists to/from stored procedures, or modeling multi-valued attributes (e.g., a list of phone numbers for a single contact).
Code Callout: Let's declare a nested table to hold a list of dependent names.

DECLARE
  -- Declaration of the nested table type
  TYPE t_dependent_list IS TABLE OF VARCHAR2(100);

  -- Variable of our new type, initialized
  l_dependents t_dependent_list := t_dependent_list();
BEGIN
  -- Populate the nested table
  l_dependents.EXTEND(3); -- Allocate space for 3 elements
  l_dependents(1) := 'Abdullah';
  l_dependents(2) := 'Usama';
  l_dependents(3) := 'Muaz';

  DBMS_OUTPUT.put_line('Total dependents: ' || l_dependents.COUNT);

  FOR i IN l_dependents.FIRST .. l_dependents.LAST LOOP
    DBMS_OUTPUT.put_line('  - ' || l_dependents(i));
  END LOOP;
END;
/

Varrays (Variable-Sized Arrays)

Varrays are similar to nested tables (integer index, dense, starting at 1) but with one critical difference: they have a fixed upper size limit. This limit must be specified in declaration.

Best for: Cases where the number of elements is known and strictly limited, and we want to enforce that limit at the data structure level.
Code Callout: Let's create a VARRAY to hold up to 5 skill tags for a job candidate.

DECLARE
  -- Declaration of the varray type with a max size of 5
  TYPE t_skill_tags IS VARRAY(5) OF VARCHAR2(50);

  -- Variable of our new type, initialized
  l_skills t_skill_tags := t_skill_tags('SQL', 'PL/SQL', 'BI Publisher');
BEGIN
  DBMS_OUTPUT.put_line('Current skills: ' || l_skills.COUNT);
  
  -- Add another skill
  l_skills.EXTEND;
  l_skills(4) := 'Python';

  DBMS_OUTPUT.put_line('--- Skills List ---');
  FOR i IN 1..l_skills.COUNT LOOP
    DBMS_OUTPUT.put_line(l_skills(i));
  END LOOP;
END;
/

Choosing the Right Collection: A Quick Comparison

Feature	Associative Array	Nested Table	Varray (Variable-Sized Array)
Index Type	`PLS_INTEGER` or `VARCHAR2`	`INTEGER`	`INTEGER`
Sparsity	Sparse (non-sequential)	Dense (sequential)	Dense (sequential)
Size Limit	Unbounded	Unbounded	Bounded (fixed upper limit)
DB Table Storage	No	Yes	Yes

Advanced Techniques & Best Practices

Understanding the types is the first step. Mastering their application for performance is next.

Supercharging Queries with `BULK COLLECT INTO`

This is the single most effective way to speed up data retrieval. Instead of a slow, row-by-row fetch inside a loop, BULK COLLECT retrieves all results from a SELECT statement into a collection in a single database call.

Code Callout: Load all employees from a department into a nested table in one go.

DECLARE
  TYPE t_emp_list IS TABLE OF employees%ROWTYPE;
  l_employees t_emp_list;
BEGIN
  -- One operation to fetch all rows, minimizing context switch
  SELECT * 
  BULK COLLECT INTO l_employees
  FROM employees
  WHERE department_id = 50; 

  DBMS_OUTPUT.put_line('Fetched ' || l_employees.COUNT || ' employees from department 50.');
END;
/

High-Speed DML with `FORALL`

FORALL is the counterpart to BULK COLLECT for DML (INSERT, UPDATE, DELETE). It takes all the elements in a collection and executes the same DML statement for every element in a single batch operation.

Code Callout: Insert a collection of records into a table, contrasting it with the inefficient row-by-row loop.

-- First, set up a simple log table
-- CREATE TABLE process_log (log_message VARCHAR2(100), log_date DATE);

DECLARE
  TYPE t_log_messages IS TABLE OF VARCHAR2(100);
  l_messages t_log_messages := t_log_messages('Process Started', 'Reading File', 'Process Ended');
BEGIN
  -- SLOW WAY: One context switch per loop iteration
  /*
  FOR i IN 1..l_messages.COUNT LOOP
    INSERT INTO process_log (log_message, log_date) VALUES (l_messages(i), SYSDATE);
  END LOOP;
  */

  -- FAST WAY: One context switch for the entire collection
  FORALL i IN 1..l_messages.COUNT
    INSERT INTO process_log (log_message, log_date) VALUES (l_messages(i), SYSDATE);

  DBMS_OUTPUT.put_line(SQL%ROWCOUNT || ' log messages inserted.');
  COMMIT;
END;
/

Essential Collection Methods

Get familiar with built-in methods to manage your collections effectively:

EXISTS(index): Returns TRUE if the specified index element exists.
COUNT: Returns the number of elements in the collection.
FIRST / LAST: Returns the first and last (lowest and highest) index numbers of the collection.
PRIOR(index) / NEXT(index): Returns the index number that precedes or succeeds the specified index.
DELETE: Removes elements from a collection.

Collections as a Performance Tool

PL/SQL collections are far more than just simple arrays. They are a fundamental component of high-performance database programming. By fetching data in bulk with BULK COLLECT and applying DML in batches with FORALL, we can transform slow, iterative code into highly efficient, scalable procedures.

The next time you find yourself writing a FOR LOOP around a SELECT or INSERT statement, challenge yourself to refactor it using collections. Your application—and your DBAs—will thank you.