ORA-06533: Subscript Beyond Count - Fix Collection Access

ORA-06533: Subscript Beyond Count

Error Overview

Error Text: ORA-06533: subscript beyond count

The ORA-06533 error is raised when PL/SQL code uses a subscript that is greater than the current number of initialized elements in a collection. Unlike ORA-06532, which enforces the hard structural limit of a VARRAY type, ORA-06533 reflects the dynamic state of the collection at the moment of access: the collection exists and has a legal type, but the subscript points beyond its currently populated elements. The fix always involves either calling EXTEND before writing to a new element, or checking COUNT before reading.

Common Causes

1. Assigning to an Element Without Calling EXTEND

The most common cause. A nested table or VARRAY is declared and initialized (or default-initialized to an empty collection), and code attempts to assign a value to index n without first calling collection.EXTEND to allocate that slot. The collection’s count is 0 (or less than n), so the assignment raises ORA-06533.

2. BULK COLLECT Into a Pre-Extended Collection

When a collection is pre-extended with EXTEND(n) and then populated with BULK COLLECT, Oracle replaces the collection contents entirely. If subsequent code uses the pre-extension count as the expected size but BULK COLLECT returned fewer rows, indexes beyond the actual row count raise ORA-06533.

3. Reading Beyond the Populated Count

Code that reads a collection element using an integer index that is within the VARRAY’s declared LIMIT but beyond the current COUNT (the number of elements actually assigned) triggers ORA-06533.

4. Parallel Code Paths That Conditionally Populate

When a collection is populated inside an IF/ELSE branch and code after the branch assumes the collection was always populated, empty-branch execution leaves count at zero, making any subscript access raise ORA-06533.

5. Nested Table vs. VARRAY Behavior Confusion

Developers sometimes expect nested tables and VARRAYs to auto-extend like associative arrays. They do not. Both require explicit EXTEND calls before direct subscript assignment. Associative arrays (INDEX BY tables) auto-create entries on assignment and never raise ORA-06533, but nested tables and VARRAYs do not share this behavior.

Diagnostic Queries

Locate the Failing Code

-- Find PL/SQL objects with recent compilation errors mentioning subscript/count
SELECT
  owner,
  name,
  type,
  line,
  text
FROM dba_errors
WHERE attribute = 'ERROR'
  AND (UPPER(text) LIKE '%SUBSCRIPT%' OR UPPER(text) LIKE '%COUNT%')
ORDER BY owner, name, line;

-- Identify the active session and SQL at the time of the error
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.module,
  s.action,
  s.sql_id,
  q.sql_text,
  s.last_call_et
FROM v$session s
LEFT JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE'
  AND s.username IS NOT NULL
ORDER BY s.last_call_et DESC;

Inspect Collection Types and Their Declared Properties

-- List all collection types in the schema with their upper bounds
SELECT
  owner,
  type_name,
  coll_type,           -- VARRAY or TABLE
  upper_bound,         -- NULL for nested tables; numeric limit for VARRAYs
  elem_type_name,
  length
FROM dba_coll_types
WHERE owner = :schema_owner
ORDER BY coll_type, type_name;

-- Find source code using EXTEND to confirm it is called before assignment
SELECT
  owner,
  name,
  type,
  line,
  text
FROM dba_source
WHERE owner = :schema_owner
  AND UPPER(text) LIKE '%EXTEND%'
  AND UPPER(text) NOT LIKE '--%'
ORDER BY owner, name, line;

Find Code That Assigns to a Collection Without EXTEND

-- Find direct numeric subscript assignments that may lack a preceding EXTEND
SELECT
  d.owner,
  d.name,
  d.type,
  d.line,
  d.text
FROM dba_source d
WHERE d.owner = :schema_owner
  AND REGEXP_LIKE(d.text, ':=\s*[^=]', 'i')
  AND REGEXP_LIKE(d.text, '\w+\s*\(\s*[0-9]+\s*\)\s*:=', 'i')
  AND UPPER(d.text) NOT LIKE '--%'
ORDER BY d.owner, d.name, d.line;

Step-by-Step Resolution

1. Call EXTEND Before Assigning to a New Element

Every time you write to a subscript that does not yet exist in the collection, call EXTEND first to allocate the slot.

DECLARE
  TYPE t_scores IS TABLE OF NUMBER;
  l_scores t_scores := t_scores();  -- Empty collection, COUNT = 0
BEGIN
  -- WRONG: raises ORA-06533 because element 1 does not exist yet
  -- l_scores(1) := 95;

  -- CORRECT: extend first, then assign
  l_scores.EXTEND;        -- COUNT is now 1
  l_scores(1) := 95;

  l_scores.EXTEND;        -- COUNT is now 2
  l_scores(2) := 87;

  l_scores.EXTEND;        -- COUNT is now 3
  l_scores(3) := 74;

  FOR i IN 1 .. l_scores.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Score ' || i || ': ' || l_scores(i));
  END LOOP;
END;
/

2. Use EXTEND(n) to Pre-Allocate Multiple Elements

DECLARE
  TYPE t_names IS TABLE OF VARCHAR2(100);
  l_names t_names := t_names();

  l_batch_size CONSTANT PLS_INTEGER := 5;
BEGIN
  -- Pre-allocate exactly the number of elements you need
  l_names.EXTEND(l_batch_size);

  -- Now safe to assign to indexes 1..5
  l_names(1) := 'Alice';
  l_names(2) := 'Bob';
  l_names(3) := 'Carol';
  l_names(4) := 'Dave';
  l_names(5) := 'Eve';

  DBMS_OUTPUT.PUT_LINE('Populated ' || l_names.COUNT || ' names.');
END;
/

3. Check COUNT Before Reading Dynamic Collections

DECLARE
  TYPE t_results IS TABLE OF VARCHAR2(200);
  l_results t_results;

  PROCEDURE load_results(p_collection IN OUT t_results) AS
  BEGIN
    SELECT department_name
    BULK COLLECT INTO p_collection
    FROM departments
    WHERE location_id = 1700;
  END;
BEGIN
  load_results(l_results);

  -- Safe: check COUNT before iterating
  IF l_results IS NOT NULL AND l_results.COUNT > 0 THEN
    FOR i IN 1 .. l_results.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(l_results(i));
    END LOOP;
  ELSE
    DBMS_OUTPUT.PUT_LINE('No results returned.');
  END IF;
END;
/

4. Use BULK COLLECT Correctly — Do Not Pre-Extend

When using BULK COLLECT, do not pre-extend the target collection. BULK COLLECT replaces the collection contents entirely and sets COUNT to the number of rows fetched. Pre-extending creates a mismatch between the allocated size and the actual row count.

DECLARE
  TYPE t_emp_ids IS TABLE OF NUMBER;
  l_emp_ids t_emp_ids;

  -- WRONG: pre-extend confuses expected size vs. actual BULK COLLECT results
  -- l_emp_ids := t_emp_ids();
  -- l_emp_ids.EXTEND(100);  -- Now COUNT = 100, but BULK COLLECT may return fewer

  -- CORRECT: let BULK COLLECT manage the collection size
BEGIN
  SELECT employee_id
  BULK COLLECT INTO l_emp_ids
  FROM employees
  WHERE department_id = 50;

  -- COUNT reflects the actual number of rows fetched
  DBMS_OUTPUT.PUT_LINE('Fetched ' || l_emp_ids.COUNT || ' employee IDs.');

  FOR i IN 1 .. l_emp_ids.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('  EMP ID: ' || l_emp_ids(i));
  END LOOP;
END;
/

5. Use BULK COLLECT With LIMIT for Large Result Sets

DECLARE
  TYPE t_emp_rec IS RECORD (
    employee_id   NUMBER,
    last_name     VARCHAR2(50),
    salary        NUMBER
  );
  TYPE t_emp_tab IS TABLE OF t_emp_rec;

  CURSOR c_emp IS
    SELECT employee_id, last_name, salary
    FROM employees
    ORDER BY employee_id;

  l_employees t_emp_tab;
  l_fetch_limit CONSTANT PLS_INTEGER := 500;
BEGIN
  OPEN c_emp;
  LOOP
    -- BULK COLLECT with LIMIT: no pre-extend needed
    FETCH c_emp BULK COLLECT INTO l_employees LIMIT l_fetch_limit;
    EXIT WHEN l_employees.COUNT = 0;

    -- Process the current batch safely
    FOR i IN 1 .. l_employees.COUNT LOOP
      -- process_employee(l_employees(i));
      NULL;
    END LOOP;
  END LOOP;
  CLOSE c_emp;
  DBMS_OUTPUT.PUT_LINE('Processing complete.');
END;
/

6. Distinguish Nested Table From Associative Array

DECLARE
  -- Associative array (INDEX BY): auto-creates on assignment, never ORA-06533
  TYPE t_aa IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
  l_aa t_aa;

  -- Nested table: requires EXTEND before assignment
  TYPE t_nt IS TABLE OF VARCHAR2(50);
  l_nt t_nt := t_nt();
BEGIN
  -- Associative array: direct assignment always works
  l_aa(1) := 'First';
  l_aa(5) := 'Fifth';   -- Gap at 2,3,4 is fine
  l_aa(100) := 'Hundredth';

  -- Nested table: must EXTEND for each new element
  l_nt.EXTEND; l_nt(1) := 'First';
  l_nt.EXTEND; l_nt(2) := 'Second';

  DBMS_OUTPUT.PUT_LINE('AA count: ' || l_aa.COUNT);
  DBMS_OUTPUT.PUT_LINE('NT count: ' || l_nt.COUNT);
END;
/

7. Handle ORA-06533 With a Named Exception

DECLARE
  TYPE t_values IS TABLE OF NUMBER;
  l_values t_values := t_values(10, 20, 30);

  l_index  PLS_INTEGER := 7;
  l_result NUMBER;
BEGIN
  l_result := l_values(l_index);
  DBMS_OUTPUT.PUT_LINE('Value: ' || l_result);

EXCEPTION
  WHEN SUBSCRIPT_BEYOND_COUNT THEN
    DBMS_OUTPUT.PUT_LINE(
      'ORA-06533: Index ' || l_index ||
      ' exceeds collection count of ' || l_values.COUNT
    );
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unhandled error: ' || SQLERRM);
    RAISE;
END;
/

Prevention Strategies

1. Use Cursor FOR Loops and BULK COLLECT as the Default Pattern

Both approaches manage collection sizing automatically. Cursor FOR loops use implicit cursors that never require EXTEND. BULK COLLECT sets COUNT to the actual row count after each fetch. These two patterns eliminate the most common ORA-06533 scenarios.

2. Prefer the COLLECT-Then-Iterate Pattern

Build collections via BULK COLLECT or a constructor, then iterate using 1 .. collection.COUNT. This guarantees the loop bound never exceeds the populated count.

3. Never Hard-Code Collection Bounds in Loop Limits

Replace hard-coded loop limits (e.g., FOR i IN 1..100) with collection.COUNT or collection.LAST to ensure the loop bound reflects actual data, not an assumed maximum.

4. Isolate EXTEND Calls to a Single Initialization Routine

Centralize collection population in one place. When population is scattered across procedures, it becomes difficult to reason about whether EXTEND has been called. A single populate_collection procedure that owns all EXTEND calls is easier to audit and maintain.

5. Consider Associative Arrays for Lookup Structures

If a collection is used as a lookup table indexed by arbitrary integers (rather than a sequentially populated list), use an associative array (INDEX BY PLS_INTEGER or INDEX BY VARCHAR2). Associative arrays do not raise ORA-06533.

-- Associative array as a keyed lookup — no EXTEND needed
DECLARE
  TYPE t_dept_map IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
  l_dept_names t_dept_map;
BEGIN
  l_dept_names(10) := 'Administration';
  l_dept_names(20) := 'Marketing';
  l_dept_names(90) := 'Executive';  -- Non-sequential key: fine

  IF l_dept_names.EXISTS(90) THEN
    DBMS_OUTPUT.PUT_LINE(l_dept_names(90));
  END IF;
END;
/

Diagnostic Scripts

These Oracle Day by Day scripts can help investigate PL/SQL runtime errors:

• gvsess.sql — Review active session SQL and execution context

Related Errors

• ORA-06502 - PL/SQL numeric or value error
• ORA-06508 - PL/SQL could not find program unit being called
• ORA-06511 - PL/SQL cursor already open
• ORA-06512 - At line (PL/SQL error stack traceback)
• ORA-06530 - Reference to uninitialized composite
• ORA-06532 - Subscript outside of limit

Emergency Response

Quick Fixes

1. Catch and log the error with collection metadata

   EXCEPTION
     WHEN SUBSCRIPT_BEYOND_COUNT THEN
       INSERT INTO error_log(error_code, error_msg, collection_count, logged_at)
       VALUES (-6533, SQLERRM, l_your_collection.COUNT, SYSTIMESTAMP);
       COMMIT;

2. Use DBMS_UTILITY to get the full error backtrace

   EXCEPTION
     WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
       RAISE;

3. Recompile the package body after applying the fix

   ALTER PACKAGE your_schema.your_package COMPILE BODY;

Post-Resolution Verification

-- Confirm no invalid objects remain after recompile
SELECT
  object_name,
  object_type,
  status,
  last_ddl_time
FROM dba_objects
WHERE owner = 'YOUR_SCHEMA'
  AND status = 'INVALID'
  AND object_type IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER')
ORDER BY object_type, object_name;

-- Confirm collection types are defined correctly
SELECT
  type_name,
  coll_type,
  upper_bound,
  elem_type_name
FROM dba_coll_types
WHERE owner = 'YOUR_SCHEMA'
ORDER BY type_name;