Oracle ORA-06532 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-06532: Subscript Outside of Limit — Causes, Fixes, and Prevention

ORA-06532 is a PL/SQL runtime error that occurs when you attempt to access a VARRAY element using an index that exceeds the maximum size declared for that VARRAY type. Unlike regular arrays in some languages, Oracle's VARRAY has a hard upper boundary set at declaration time, and any attempt to read or write beyond that boundary triggers this error immediately. Because this is a runtime error rather than a compile-time error, it can easily slip through code reviews and only surface in production.

Top 3 Causes

1. Accessing an Index Beyond the VARRAY's Declared Limit

The most common cause is simply trying to use an index larger than the LIMIT of the VARRAY. If you declare a VARRAY with a max size of 5 and try to access index 6, Oracle throws ORA-06532.

DECLARE
  TYPE num_varray IS VARRAY(5) OF NUMBER;
  v_nums num_varray := num_varray(10, 20, 30, 40, 50);
BEGIN
  -- This will raise ORA-06532 because LIMIT is 5
  v_nums.EXTEND;       -- This itself will raise ORA-06532!
  v_nums(6) := 60;     -- Index 6 exceeds LIMIT of 5
  DBMS_OUTPUT.PUT_LINE(v_nums(6));
END;
/

Fix: Declare the VARRAY with a large enough limit, or validate the index before access.

DECLARE
  TYPE num_varray IS VARRAY(10) OF NUMBER; -- Increased limit
  v_nums num_varray := num_varray(10, 20, 30, 40, 50);
BEGIN
  IF v_nums.COUNT < v_nums.LIMIT THEN
    v_nums.EXTEND;
    v_nums(v_nums.LAST) := 60;
    DBMS_OUTPUT.PUT_LINE('Added: ' || v_nums(v_nums.LAST));
  ELSE
    DBMS_OUTPUT.PUT_LINE('VARRAY is full. LIMIT = ' || v_nums.LIMIT);
  END IF;
END;
/

2. Using EXTEND Beyond the VARRAY's Maximum Size

Even the EXTEND method itself will trigger ORA-06532 if the collection has already reached its declared LIMIT. Developers sometimes call EXTEND inside a loop without checking remaining capacity.

DECLARE
  TYPE small_array IS VARRAY(3) OF VARCHAR2(20);
  v_arr small_array := small_array('A', 'B', 'C');
BEGIN
  -- Already at LIMIT=3, EXTEND will raise ORA-06532
  FOR i IN 1..5 LOOP
    v_arr.EXTEND;          -- Fails on 4th iteration
    v_arr(v_arr.LAST) := 'Item_' || i;
  END LOOP;
END;
/

Fix: Always check COUNT < LIMIT before calling EXTEND.

DECLARE
  TYPE small_array IS VARRAY(10) OF VARCHAR2(20);
  v_arr small_array := small_array('A', 'B', 'C');
BEGIN
  FOR i IN 1..5 LOOP
    IF v_arr.COUNT < v_arr.LIMIT THEN
      v_arr.EXTEND;
      v_arr(v_arr.LAST) := 'Item_' || i;
      DBMS_OUTPUT.PUT_LINE('Inserted: ' || v_arr(v_arr.LAST));
    ELSE
      DBMS_OUTPUT.PUT_LINE('Cannot add more. LIMIT reached: ' || v_arr.LIMIT);
      EXIT;
    END IF;
  END LOOP;
END;
/

3. Hardcoded Loop Upper Bounds

Using a hardcoded number as a loop boundary instead of the collection's .COUNT or .LIMIT attribute is a classic mistake that leads to ORA-06532 when data volume changes.

DECLARE
  TYPE emp_array IS VARRAY(20) OF VARCHAR2(100);
  v_emps emp_array := emp_array();
BEGIN
  -- Populate with only 3 elements
  v_emps.EXTEND(3);
  v_emps(1) := 'John'; v_emps(2) := 'Jane'; v_emps(3) := 'Bob';

  -- Hardcoded 10 causes ORA-06532 since VARRAY has only 3 elements initialized
  FOR i IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE(v_emps(i)); -- Fails at i=4
  END LOOP;
END;
/

Fix: Always use .COUNT for the current element count.

DECLARE
  TYPE emp_array IS VARRAY(20) OF VARCHAR2(100);
  v_emps emp_array := emp_array();
BEGIN
  v_emps.EXTEND(3);
  v_emps(1) := 'John'; v_emps(2) := 'Jane'; v_emps(3) := 'Bob';

  -- Safe: use .COUNT as the upper bound
  FOR i IN 1..v_emps.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(i || ': ' || v_emps(i));
  END LOOP;
END;
/

Quick Fix Solutions

Increase VARRAY size at declaration if the limit is too restrictive.
Replace VARRAY with Nested Table if the maximum size is truly unpredictable — Nested Tables have no upper limit.
Always use .COUNT and .LIMIT instead of hardcoded numbers in loops.
Add explicit exception handling using the predefined SUBSCRIPT_OUTSIDE_LIMIT exception name.

EXCEPTION
  WHEN SUBSCRIPT_OUTSIDE_LIMIT THEN
    DBMS_OUTPUT.PUT_LINE('ORA-06532: Index exceeds VARRAY limit of ' || v_arr.LIMIT);
  WHEN SUBSCRIPT_BEYOND_COUNT THEN
    DBMS_OUTPUT.PUT_LINE('ORA-06533: Index exceeds current element count of ' || v_arr.COUNT);

Prevention Tips

Never hardcode collection boundaries. Always rely on .COUNT, .LIMIT, and .LAST attributes to make your code resilient to data volume changes.
Prefer Nested Tables over VARRAYs when the data size is dynamic or unknown at design time. If you must use a VARRAY, set the limit generously and validate before every EXTEND or index access.

Related Errors

Error Code	Description
ORA-06533	Subscript beyond count — index exceeds initialized elements but not LIMIT
ORA-06531	Reference to uninitialized collection — collection not initialized before use
ORA-06502	Numeric or value error — data type or size mismatch in collection element

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community