ORA-06532: Subscript Outside of Limit - Fix Collection Errors

ORA-06532: Subscript Outside of Limit

Error Overview

Error Text: ORA-06532: subscript outside of limit

The ORA-06532 error is raised when PL/SQL code accesses a collection element using a subscript (index) that violates the collection’s defined upper bound. This error applies primarily to VARRAYs, which have a fixed maximum size declared at type definition time. It also occurs when a negative or zero subscript is used on any collection type. Unlike ORA-06533, which signals access beyond the current element count, ORA-06532 signals a violation of the hard structural limit of the collection type.

Common Causes

1. VARRAY Fixed Upper Bound Exceeded

A VARRAY is declared with a maximum number of elements (e.g., VARRAY(10) OF VARCHAR2(100)). Attempting to store or read element 11 raises ORA-06532 regardless of how many elements are currently populated.

2. Negative or Zero Subscript

PL/SQL collection subscripts must be positive integers. Passing a zero or negative value — whether from a loop counter going below 1, a calculation error, or a user-supplied parameter — raises ORA-06532.

3. Accessing a Deleted Nested Table Element

Nested tables support the DELETE method, which removes specific elements and leaves gaps. Accessing a deleted element directly by its former index raises ORA-06532 because the subscript no longer refers to a valid element within the collection’s limit structure.

4. Off-By-One in Loop Bounds

Loops that iterate from 0 to collection.COUNT (inclusive) instead of 1 to collection.COUNT hit a zero-based subscript on the first iteration and raise ORA-06532.

5. Hard-Coded Index Beyond VARRAY Size

Code that assumes a VARRAY is always fully populated and uses a literal index larger than the current element count, or larger than the declared maximum, triggers the error.

Diagnostic Queries

Identify the Failing Program Unit and Line

-- Find PL/SQL compilation errors referencing collections
SELECT
  owner,
  name,
  type,
  line,
  text
FROM dba_errors
WHERE attribute = 'ERROR'
  AND (UPPER(text) LIKE '%SUBSCRIPT%' OR UPPER(text) LIKE '%LIMIT%')
ORDER BY owner, name, line;

-- Check runtime errors captured in the alert log or trace
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.module,
  s.action,
  s.sql_id,
  s.last_call_et AS seconds_active
FROM v$session s
WHERE s.status = 'ACTIVE'
  AND s.username IS NOT NULL
ORDER BY s.last_call_et DESC;

Inspect Collection Type Definitions

-- Find VARRAY type definitions and their declared limits
SELECT
  owner,
  type_name,
  typecode,
  attributes,
  methods,
  predefined,
  incomplete
FROM dba_types
WHERE typecode = 'COLLECTION'
  AND owner = :schema_owner
ORDER BY type_name;

-- Get collection element type and limit from DBA_COLL_TYPES
SELECT
  owner,
  type_name,
  coll_type,
  upper_bound,   -- Maximum elements for VARRAY; NULL for nested table
  elem_type_name,
  elem_type_owner,
  length,
  precision,
  scale
FROM dba_coll_types
WHERE owner = :schema_owner
ORDER BY type_name;

Review Source Code for Risky Subscript Patterns

-- Find PL/SQL source lines using hard-coded numeric subscripts
SELECT
  owner,
  name,
  type,
  line,
  text
FROM dba_source
WHERE owner = :schema_owner
  AND REGEXP_LIKE(text, '\(\s*[0-9]+\s*\)', 'i')
  AND UPPER(text) NOT LIKE '--%'  -- Exclude comments
ORDER BY owner, name, line;

-- Find loops starting from 0 (common off-by-one for collections)
SELECT
  owner,
  name,
  type,
  line,
  text
FROM dba_source
WHERE owner = :schema_owner
  AND REGEXP_LIKE(text, '\bFOR\b.+\bIN\b\s+0\s*\.\.', 'i')
ORDER BY owner, name, line;

Step-by-Step Resolution

1. Validate Subscript Before Access

Always check that a subscript is within bounds before using it to access a collection element.

DECLARE
  -- VARRAY with a fixed upper bound of 5
  TYPE t_scores IS VARRAY(5) OF NUMBER;
  l_scores t_scores := t_scores(90, 85, 78, 92, 88);

  l_index NUMBER := 6;  -- Intentionally out of range
BEGIN
  -- Safe access pattern: validate bounds first
  IF l_index >= 1 AND l_index <= l_scores.LIMIT THEN
    DBMS_OUTPUT.PUT_LINE('Score: ' || l_scores(l_index));
  ELSE
    DBMS_OUTPUT.PUT_LINE(
      'Index ' || l_index ||
      ' is outside VARRAY limit of ' || l_scores.LIMIT
    );
  END IF;
END;
/

2. Use FIRST / LAST / NEXT for Safe Iteration

Avoid hard-coded loop bounds. Use the collection’s own navigation methods to iterate safely, especially over nested tables that may have gaps from DELETE operations.

DECLARE
  TYPE t_names IS TABLE OF VARCHAR2(100);
  l_names t_names := t_names('Alice', 'Bob', 'Carol', 'Dave');

  l_idx PLS_INTEGER;
BEGIN
  -- Delete the second element, creating a gap
  l_names.DELETE(2);

  -- WRONG: numeric FOR loop skips gaps and may hit deleted slot
  -- FOR i IN 1 .. l_names.COUNT LOOP  -- COUNT is now 3, but index 2 is gone
  --   DBMS_OUTPUT.PUT_LINE(l_names(i));  -- ORA-06532 on i=3 (mapped to old 4)
  -- END LOOP;

  -- CORRECT: use FIRST/NEXT to navigate, skipping deleted elements
  l_idx := l_names.FIRST;
  WHILE l_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('Index ' || l_idx || ': ' || l_names(l_idx));
    l_idx := l_names.NEXT(l_idx);
  END LOOP;
END;
/

3. Use EXISTS Before Accessing Potentially Deleted Elements

DECLARE
  TYPE t_products IS TABLE OF VARCHAR2(200);
  l_products t_products := t_products('Widget', 'Gadget', 'Doohickey');

  l_target_index PLS_INTEGER := 2;
BEGIN
  -- Remove an element
  l_products.DELETE(l_target_index);

  -- Safe access: check EXISTS before subscript
  IF l_products.EXISTS(l_target_index) THEN
    DBMS_OUTPUT.PUT_LINE('Product: ' || l_products(l_target_index));
  ELSE
    DBMS_OUTPUT.PUT_LINE(
      'Element at index ' || l_target_index || ' has been deleted.'
    );
  END IF;
END;
/

4. Respect VARRAY Limits When Populating

DECLARE
  TYPE t_readings IS VARRAY(10) OF NUMBER;
  l_readings t_readings := t_readings();

  -- Simulate incoming data that may exceed the VARRAY limit
  TYPE t_raw IS TABLE OF NUMBER;
  l_raw t_raw := t_raw(1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8, 9.9, 10.0, 11.1);
BEGIN
  FOR i IN 1 .. l_raw.COUNT LOOP
    -- Stop populating when the VARRAY limit is reached
    EXIT WHEN i > l_readings.LIMIT;
    l_readings.EXTEND;
    l_readings(i) := l_raw(i);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE(
    'Loaded ' || l_readings.COUNT || ' of ' || l_readings.LIMIT || ' max readings.'
  );
END;
/

5. Handle ORA-06532 Gracefully in Exception Blocks

DECLARE
  TYPE t_codes IS VARRAY(3) OF VARCHAR2(10);
  l_codes t_codes := t_codes('A001', 'B002', 'C003');

  l_result VARCHAR2(10);
BEGIN
  -- Attempt to access a subscript supplied at runtime
  l_result := l_codes(5);  -- Will raise ORA-06532
  DBMS_OUTPUT.PUT_LINE(l_result);

EXCEPTION
  WHEN SUBSCRIPT_OUTSIDE_LIMIT THEN
    DBMS_OUTPUT.PUT_LINE(
      'ORA-06532: Subscript out of range. ' ||
      'Collection limit is ' || l_codes.LIMIT ||
      ' and current count is ' || l_codes.COUNT
    );
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
    RAISE;
END;
/

Prevention Strategies

1. Always Use LIMIT for VARRAY Upper Bound Checks

Use collection.LIMIT (not a hard-coded constant) when checking bounds. If the VARRAY type is ever redefined with a larger limit, code using LIMIT adapts automatically.

2. Prefer Nested Tables for Variable-Length Data

If the number of elements is not known at compile time, use a nested table (unbounded) rather than a VARRAY. Nested tables grow dynamically and do not have a hard upper bound, eliminating the LIMIT-related cause of ORA-06532.

3. Encapsulate Collection Access in Helper Functions

Wrap collection subscript access in a function that validates bounds and returns NULL or a default on out-of-range access, preventing the error from propagating to application code.

CREATE OR REPLACE FUNCTION safe_varray_get(
  p_collection IN SYS.ODCINUMBERLIST,  -- or your custom VARRAY type
  p_index      IN PLS_INTEGER,
  p_default    IN NUMBER DEFAULT NULL
) RETURN NUMBER AS
BEGIN
  IF p_index >= 1
     AND p_index <= p_collection.LIMIT
     AND p_collection.EXISTS(p_index)
  THEN
    RETURN p_collection(p_index);
  ELSE
    RETURN p_default;
  END IF;
EXCEPTION
  WHEN SUBSCRIPT_OUTSIDE_LIMIT THEN
    RETURN p_default;
END;
/

4. Code Review Checklist for Collections

• Every loop over a collection starts at collection.FIRST, not 0 or 1
• Every numeric index is validated against collection.LIMIT and collection.EXISTS
• DELETE operations are followed by FIRST/NEXT navigation, not integer FOR loops
• VARRAY types are sized generously to accommodate maximum realistic data

Diagnostic Scripts

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

• gvsess.sql — Review active session details and SQL being executed

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-06533 - Subscript beyond count

Emergency Response

Quick Fixes

1. Catch the error and log the bad subscript for triage

   EXCEPTION
     WHEN SUBSCRIPT_OUTSIDE_LIMIT THEN
       INSERT INTO error_log(error_code, error_msg, logged_at)
       VALUES (-6532, SQLERRM, SYSTIMESTAMP);
       COMMIT;

2. Identify the package and line raising the error from the call stack

   -- Run from the session experiencing the error
   SELECT * FROM TABLE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

3. Recompile the offending package after a code fix

   ALTER PACKAGE your_schema.your_package COMPILE BODY;

Post-Resolution Verification

-- Confirm no remaining compilation errors
SELECT
  object_name,
  object_type,
  status
FROM dba_objects
WHERE owner = 'YOUR_SCHEMA'
  AND status = 'INVALID'
  AND object_type IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
ORDER BY object_type, object_name;

-- Verify collection type limits are as expected
SELECT
  type_name,
  coll_type,
  upper_bound,
  elem_type_name
FROM dba_coll_types
WHERE owner = 'YOUR_SCHEMA'
ORDER BY type_name;