Oracle ORA-12726 Error: Causes and Solutions Complete Guide

ORA-12726: Unmatched Bracket in Regular Expression

ORA-12726 is thrown by Oracle's regular expression engine when a bracket [ or ] inside a regex pattern is not properly paired. This error surfaces in functions such as REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_REPLACE, REGEXP_INSTR, and REGEXP_COUNT whenever the pattern string contains an unbalanced bracket. Oracle cannot parse the malformed pattern and immediately raises the error before executing the query.

---

Top 3 Causes and Fixes

Cause 1: Missing Closing Bracket ] in a Character Class

The most common cause is simply forgetting to close a character class with ].

-- WRONG: triggers ORA-12726
SELECT *
FROM employees
WHERE REGEXP_LIKE(first_name, '[A-Z');

-- CORRECT: bracket properly closed
SELECT *
FROM employees
WHERE REGEXP_LIKE(first_name, '[A-Z]');

-- Multiple character classes used correctly
SELECT REGEXP_REPLACE('abc123', '[0-9]+', '#') AS result
FROM dual;
-- Result: abc#

Fix: Always pair [ with ] immediately when typing and then fill in the content.

---

Cause 2: Incorrect Use of POSIX Character Classes

Oracle supports POSIX classes like [:alpha:], [:digit:], and [:space:], but they must be wrapped inside an outer pair of brackets: [[:alpha:]].

-- WRONG: missing outer brackets → ORA-12726
SELECT *
FROM employees
WHERE REGEXP_LIKE(first_name, '[:alpha:]+');

-- CORRECT: outer brackets included
SELECT *
FROM employees
WHERE REGEXP_LIKE(first_name, '[[:alpha:]]+');

-- Practical example: validate uppercase first letter
SELECT first_name
FROM employees
WHERE REGEXP_LIKE(first_name, '^[[:upper:]][[:lower:]]+$');

-- Email format validation
SELECT email
FROM employees
WHERE REGEXP_LIKE(email,
  '^[[:alnum:]._-]+@[[:alnum:].-]+\.[[:alpha:]]{2,}$');

Fix: Remember the double-bracket rule — [[:classname:]] — whenever using POSIX classes.

---

Cause 3: Escape Handling Confusion in Dynamic SQL

When building regex patterns dynamically in PL/SQL or application code, backslash escaping can cause brackets to be misinterpreted, resulting in an unmatched bracket.

-- Testing a dynamic pattern safely with dual first
SELECT REGEXP_SUBSTR('Order[001]', '\[[^\]]+\]') AS bracket_text
FROM dual;
-- Result: [001]

-- Safe dynamic pattern execution with error handling
DECLARE
  v_pattern VARCHAR2(200) := '[[:alnum:]]+';
  v_count   NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO v_count
  FROM employees
  WHERE REGEXP_LIKE(first_name, v_pattern);

  DBMS_OUTPUT.PUT_LINE('Matched rows: ' || v_count);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Regex error: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('Pattern used: ' || v_pattern);
END;
/

Fix: Always validate dynamic patterns against dual before running against large tables.

---

Quick Fix Checklist

1. Count [ and ] in your pattern — they must be equal.
2. Every POSIX class must follow the [[:classname:]] double-bracket syntax.
3. To match a literal [ or ], escape it: \[ or \].
4. Test all new patterns on dual with a sample string before production use.

---

Prevention Tips

Validate patterns on dual first:

-- Always test here before touching real tables
SELECT REGEXP_SUBSTR('SampleText123', '[[:alpha:]]+') AS test_result
FROM dual;

Centralize patterns in a PL/SQL package:

CREATE OR REPLACE PACKAGE regex_constants AS
  C_EMAIL   CONSTANT VARCHAR2(300) := '^[[:alnum:]._-]+@[[:alnum:].-]+\.[[:alpha:]]{2,}$';
  C_PHONE   CONSTANT VARCHAR2(300) := '^[0-9]{2,3}-[0-9]{3,4}-[0-9]{4}$';
  C_ALPHA   CONSTANT VARCHAR2(300) := '^[[:alpha:]]+$';
  C_NUMERIC CONSTANT VARCHAR2(300) := '^[[:digit:]]+$';
END regex_constants;
/

Centralizing patterns makes them reusable, easier to review, and reduces the chance of typos slipping into production SQL.

---

Related Oracle Errors

Error Code	Description
ORA-12725	Unmatched parentheses () in regular expression
ORA-12727	Invalid back reference in regular expression
ORA-12728	Invalid range in regular expression (e.g., [z-a])
ORA-12729	Invalid character class name in regular expression

All of these errors originate from the same regex parsing stage and are often encountered together when working with complex patterns in Oracle SQL.

---

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