Oracle ORA-12722 Error: Causes and Solutions Complete Guide

ORA-12722: Regular Expression Internal Error in Oracle

ORA-12722 is thrown when Oracle's internal regular expression engine encounters an unexpected failure while processing a regex pattern. It typically surfaces in functions like REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, and REGEXP_COUNT. Unlike more specific regex errors (ORA-12725, ORA-12726), this error signals a deeper internal engine-level problem.

---

Top 3 Causes

1. Malformed Regular Expression Pattern

The most common cause is passing a syntactically invalid regex pattern to an Oracle regex function. Remember that Oracle follows the POSIX ERE standard — not Perl-Compatible Regular Expressions (PCRE) used in Python or Java.

-- BAD: Unclosed parenthesis causes ORA-12722
SELECT REGEXP_LIKE('Hello', '(Hello')
FROM DUAL;

-- BAD: Invalid quantifier range (min > max)
SELECT REGEXP_SUBSTR('abc', '[a-z]{3,1}')
FROM DUAL;

-- GOOD: Properly closed parenthesis
SELECT CASE WHEN REGEXP_LIKE('Hello World', '(Hello)\s(World)')
            THEN 'MATCH' ELSE 'NO MATCH'
       END AS result
FROM DUAL;

-- GOOD: Valid POSIX character class usage
SELECT REGEXP_SUBSTR('test123', '[[:alpha:]]+') AS letters_only
FROM DUAL;

2. Invalid match_parameter Argument

Oracle regex functions accept an optional match_parameter argument. Passing unsupported characters causes an internal parsing failure. Valid values are: i, c, n, m, x.

-- BAD: 'z' is not a valid match_parameter
SELECT REGEXP_REPLACE('Hello World', 'hello', 'Hi', 1, 0, 'z')
FROM DUAL;

-- GOOD: 'i' for case-insensitive matching
SELECT REGEXP_REPLACE('Hello World', 'hello', 'Hi', 1, 0, 'i') AS result
FROM DUAL;

-- GOOD: 'm' for multiline mode (^ and $ match per line)
SELECT REGEXP_COUNT('Line1' || CHR(10) || 'Line2', '^Line', 1, 'm') AS cnt
FROM DUAL;

-- Safe dynamic validation before using match_parameter
DECLARE
    v_param VARCHAR2(10) := 'i';
BEGIN
    IF REGEXP_LIKE(v_param, '^[icnmx]+$') THEN
        DBMS_OUTPUT.PUT_LINE('Valid parameter: ' || v_param);
    ELSE
        DBMS_OUTPUT.PUT_LINE('INVALID match_parameter — aborting.');
    END IF;
END;
/

3. Oracle Engine Bug in Older Versions

Certain Oracle releases (notably early 10g and 11g builds) contain known bugs in the regex engine that trigger ORA-12722 even with valid patterns. Check your patch level and consult Oracle My Support (MOS) for applicable fixes.

-- Check Oracle version and patch history
SELECT * FROM v$version;

SELECT patch_id, description, action, action_time
FROM dba_registry_history
WHERE action = 'APPLY'
ORDER BY action_time DESC;

-- Workaround: Replace complex REGEXP with LIKE + SUBSTR until patch is applied
-- Instead of: WHERE REGEXP_LIKE(code, '^[A-Z]{2}[0-9]{6}$')
SELECT *
FROM orders
WHERE LENGTH(order_code) = 8
  AND REGEXP_LIKE(SUBSTR(order_code, 1, 2), '^[A-Z]{2}$')
  AND REGEXP_LIKE(SUBSTR(order_code, 3, 6), '^[0-9]{6}$');

---

Quick Fix Solutions

-- Validate a regex pattern safely using a wrapper function
CREATE OR REPLACE FUNCTION is_valid_regexp(p_pattern IN VARCHAR2)
RETURN VARCHAR2 IS
    v_dummy NUMBER;
BEGIN
    SELECT REGEXP_COUNT('validation_test', p_pattern)
    INTO v_dummy FROM DUAL;
    RETURN 'VALID';
EXCEPTION
    WHEN OTHERS THEN RETURN 'INVALID: ' || SQLERRM;
END;
/

-- Test it
SELECT is_valid_regexp('^[A-Z]{2}[0-9]+$') AS test1,
       is_valid_regexp('(bad_pattern')      AS test2
FROM DUAL;

---

Prevention Tips

1. Always validate regex patterns in development first. Use the is_valid_regexp wrapper function shown above as a standard gate before deploying any pattern to production.

2. Keep Oracle patched. Apply Oracle's quarterly Release Updates (RU) or Patch Set Updates (PSU) regularly. Monitor Oracle My Support for regex-related bugs specific to your version. Run patch validation in a test environment before production rollout.

---

Related Errors

Error Code	Description
ORA-12723	Regular expression too complex
ORA-12725	Unmatched parentheses in regular expression
ORA-12726	Unmatched bracket in regular expression
ORA-12728	Invalid range in regular expression

---

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