Oracle ORA-00036 Error: Causes and Solutions Complete Guide

ORA-00036: Maximum Number of Recursive SQL Levels Exceeded

ORA-00036 occurs when Oracle's internal recursive SQL call stack exceeds its maximum allowed depth, which defaults to 2000 levels. This typically happens when triggers, stored procedures, or views call each other in a circular pattern, creating an infinite loop of recursive SQL execution.

---

Top 3 Causes

1. Circular Trigger Dependencies

Two or more triggers fire each other indefinitely. For example, a trigger on TABLE_A updates TABLE_B, and a trigger on TABLE_B updates TABLE_A.

-- Diagnose circular triggers
SELECT trigger_name, triggering_event, table_name, status
FROM user_triggers
WHERE table_name IN ('TABLE_A', 'TABLE_B');

-- Temporarily disable the offending trigger to isolate the issue
ALTER TRIGGER trg_table_a_update DISABLE;

Fix: Use a package-level flag to prevent re-entry:

-- Guard package
CREATE OR REPLACE PACKAGE pkg_guard AS
    g_running BOOLEAN := FALSE;
END pkg_guard;
/

-- Trigger with re-entry protection
CREATE OR REPLACE TRIGGER trg_table_a_update
AFTER UPDATE ON table_a
FOR EACH ROW
BEGIN
    IF NOT pkg_guard.g_running THEN
        pkg_guard.g_running := TRUE;
        BEGIN
            UPDATE table_b SET col1 = :NEW.col1 WHERE id = :NEW.id;
            pkg_guard.g_running := FALSE;
        EXCEPTION
            WHEN OTHERS THEN
                pkg_guard.g_running := FALSE;
                RAISE;
        END;
    END IF;
END;
/

---

2. Infinite Recursive PL/SQL Procedures or Functions

A procedure or function calls itself without a proper termination condition, causing the call stack to grow until ORA-00036 is thrown.

-- BAD: No termination condition (will cause ORA-00036)
CREATE OR REPLACE FUNCTION bad_func(p_n NUMBER) RETURN NUMBER IS
BEGIN
    RETURN bad_func(p_n + 1); -- No base case!
END;
/

-- GOOD: Proper base case and depth limit
CREATE OR REPLACE FUNCTION good_func(
    p_n     NUMBER,
    p_depth NUMBER DEFAULT 0
) RETURN NUMBER IS
BEGIN
    IF p_depth > 100 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Max recursion depth exceeded');
    END IF;
    IF p_n <= 0 THEN RETURN 0; END IF;
    RETURN p_n + good_func(p_n - 1, p_depth + 1);
END;
/

Better alternative: Use Oracle's built-in hierarchical query instead of recursive PL/SQL:

-- Use CONNECT BY instead of recursive procedures
SELECT LEVEL, employee_id, last_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

---

3. Circular View References

Views that reference each other in a cycle cause Oracle to resolve them recursively until the limit is hit.

-- Check view dependencies for circular references
SELECT name, referenced_name, referenced_type
FROM user_dependencies
WHERE type = 'VIEW'
  AND referenced_type = 'VIEW'
ORDER BY name;

-- Fix: Rewrite the view to reference the base table directly
CREATE OR REPLACE VIEW view_a AS
SELECT col1, col2
FROM base_table          -- reference the actual table, not view_b
WHERE status = 'ACTIVE';

---

Quick Fix Summary

Cause	Quick Action
Circular triggers	Disable one trigger; add re-entry guard flag
Infinite recursion	Add base case + depth limit parameter
Circular views	Rewrite to reference base tables directly

---

Prevention Tips

1. Audit dependencies regularly — Query DBA_DEPENDENCIES periodically to detect circular references before they reach production.

SELECT owner, name, type, referenced_name, referenced_type
FROM dba_dependencies
WHERE type IN ('TRIGGER','PROCEDURE','FUNCTION','VIEW')
  AND owner = 'YOUR_SCHEMA'
ORDER BY name;

1. Enforce coding standards — Make termination conditions and maximum depth parameters mandatory in all recursive PL/SQL code reviews. Prefer set-based SQL (CONNECT BY, recursive CTEs) over procedural recursion whenever possible.

---

📖 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.