Oracle ORA-06533 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-06533: Subscript Beyond Count – Causes, Fixes & Prevention

ORA-06533 is a PL/SQL runtime error that occurs when your code tries to access a collection element using an index that exceeds the collection's current element count (COUNT). This applies to Oracle collection types such as VARRAYs and Nested Tables. Unlike a simple "array out of bounds" in other languages, Oracle raises this as a named exception (SUBSCRIPT_BEYOND_COUNT) that can be caught and handled explicitly.

Top 3 Causes

Cause 1: Accessing Elements Without Calling EXTEND First

The most common cause is assigning values to a collection index without first reserving space using the EXTEND method.

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  v_nums num_tab := num_tab(); -- empty collection
BEGIN
  -- This raises ORA-06533: no elements exist yet
  -- v_nums(1) := 42;

  -- Correct: extend first, then assign
  v_nums.EXTEND(3);
  v_nums(1) := 10;
  v_nums(2) := 20;
  v_nums(3) := 30;

  DBMS_OUTPUT.PUT_LINE('Count: ' || v_nums.COUNT); -- Output: 3
END;
/

Always call EXTEND before assigning values by index, or use the collection constructor to initialize values inline.

Cause 2: Hardcoded or Incorrectly Calculated Loop Bounds

Using a fixed number or a variable from a different context as the loop upper limit instead of the collection's actual COUNT leads to ORA-06533 when the collection has fewer elements than expected.

DECLARE
  TYPE str_tab IS TABLE OF VARCHAR2(50);
  v_items str_tab := str_tab('Alpha', 'Beta', 'Gamma'); -- 3 elements
BEGIN
  -- Dangerous: hardcoded upper bound
  -- FOR i IN 1..10 LOOP
  --   DBMS_OUTPUT.PUT_LINE(v_items(i)); -- ORA-06533 at i=4
  -- END LOOP;

  -- Safe: use FIRST and LAST attributes
  FOR i IN v_items.FIRST..v_items.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(v_items(i));
  END LOOP;
END;
/

Cause 3: Iterating a Sparse Collection After DELETE

Nested Tables allow mid-collection deletions via DELETE, but the indexes are not re-sequenced afterward. After deletion, COUNT reflects the remaining elements, but gaps in the index sequence remain. Looping with 1..COUNT can skip valid indexes or, in edge cases, trigger related subscript errors.

DECLARE
  TYPE fruit_tab IS TABLE OF VARCHAR2(30);
  v_fruits fruit_tab := fruit_tab('Apple', 'Banana', 'Cherry', 'Date');
  v_idx    PLS_INTEGER;
BEGIN
  v_fruits.DELETE(2); -- Remove 'Banana'; index 2 is now a gap

  -- Risky: 1..COUNT approach on a sparse collection
  -- FOR i IN 1..v_fruits.COUNT LOOP ...

  -- Safe: use FIRST/NEXT to walk the sparse collection
  v_idx := v_fruits.FIRST;
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(v_idx || ': ' || v_fruits(v_idx));
    v_idx := v_fruits.NEXT(v_idx);
  END LOOP;
END;
/

Quick Fix Solutions

Add a guard check and use the SUBSCRIPT_BEYOND_COUNT named exception in production code:

DECLARE
  TYPE num_list IS TABLE OF NUMBER;
  v_data num_list := num_list(100, 200, 300);
BEGIN
  -- Guard: verify collection is populated before access
  IF v_data IS NOT NULL AND v_data.COUNT > 0 THEN
    FOR i IN v_data.FIRST..v_data.LAST LOOP
      IF v_data.EXISTS(i) THEN
        DBMS_OUTPUT.PUT_LINE('Value: ' || v_data(i));
      END IF;
    END LOOP;
  END IF;

EXCEPTION
  WHEN SUBSCRIPT_BEYOND_COUNT THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: Index exceeded collection COUNT.');
  WHEN SUBSCRIPT_OUTSIDE_LIMIT THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: Index exceeded declared VARRAY limit.');
END;
/

Prevention Tips

1. Always use FIRST, LAST, and EXISTS for collection iteration.
Never hardcode loop boundaries. Use collection.FIRST..collection.LAST with an EXISTS(i) guard inside the loop body to safely handle sparse collections after DELETE operations.

2. Write boundary-value unit tests for all PL/SQL collection logic.
Test with an empty collection, a single-element collection, a full-capacity collection, and a sparse (post-DELETE) collection. Frameworks like utPLSQL make this straightforward and catch ORA-06533 vulnerabilities before they reach production.

Related Oracle Errors

Error Code	Description
ORA-06531	Reference to uninitialized collection (NULL collection — no constructor called)
ORA-06532	Subscript outside of limit (index exceeds VARRAY declared maximum size)
ORA-01403	No data found (common companion when loading collections from cursors)

📖 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