Oracle ORA-12723 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12723: Regular Expression Compile Error — Causes, Fixes & Prevention

ORA-12723 is thrown by Oracle Database when the regular expression engine fails to compile a pattern passed to functions like REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_REPLACE, REGEXP_INSTR, or REGEXP_COUNT. Oracle's regex engine is based on the POSIX Extended Regular Expression (ERE) standard, which means some patterns valid in Perl, Python, or Java may not be supported. When the engine cannot interpret the given pattern, execution halts immediately and ORA-12723 is returned.

Top 3 Causes and Fixes

Cause 1: Invalid Regex Syntax (Unbalanced Brackets or Misplaced Quantifiers)

The most common trigger is malformed regex syntax — mismatched parentheses, unclosed brackets, or a quantifier (+, *, ?) with no preceding expression.

-- BAD: Quantifier with no preceding expression
SELECT REGEXP_SUBSTR('Hello123', '+[0-9]') FROM DUAL;
-- Raises ORA-12723

-- BAD: Unclosed bracket
SELECT * FROM DUAL WHERE REGEXP_LIKE('test', '[abc');
-- Raises ORA-12723

-- GOOD: Corrected syntax
SELECT REGEXP_SUBSTR('Hello123', '[A-Za-z]+[0-9]+') FROM DUAL;

-- GOOD: Properly closed bracket
SELECT * FROM DUAL WHERE REGEXP_LIKE('test', '[abc]');

-- Step-by-step pattern debugging on DUAL
SELECT REGEXP_SUBSTR('ORA-12723', '[A-Z]+')        AS step1 FROM DUAL;
SELECT REGEXP_SUBSTR('ORA-12723', '[A-Z]+-[0-9]+') AS step2 FROM DUAL;
SELECT REGEXP_SUBSTR('ORA-12723', 'ORA-[0-9]{5}')  AS step3 FROM DUAL;

Fix: Always validate patterns incrementally. Start with the simplest possible expression and add complexity one piece at a time before deploying to production.

Cause 2: Using Unsupported Regex Features

Oracle's POSIX ERE engine does not support lookahead ((?=...)), lookbehind ((?<=...)), named groups ((?P<name>...)), or other Perl-style extensions. Copying patterns from Python or Java directly into Oracle SQL is a frequent source of ORA-12723.

-- BAD: Perl-style lookahead (not supported in Oracle)
-- REGEXP_SUBSTR(col, '(?<=ORA-)\d+')  --> ORA-12723

-- GOOD: Oracle-compatible alternative using capture groups
SELECT REGEXP_SUBSTR('ORA-12723: error', 'ORA-([0-9]+)', 1, 1, NULL, 1) AS error_num
FROM DUAL;
-- Returns: 12723

-- GOOD: Named groups replaced with numbered capture groups
SELECT
  REGEXP_SUBSTR('2024-07-15', '([0-9]{4})-([0-9]{2})-([0-9]{2})', 1, 1, NULL, 1) AS yr,
  REGEXP_SUBSTR('2024-07-15', '([0-9]{4})-([0-9]{2})-([0-9]{2})', 1, 1, NULL, 2) AS mo,
  REGEXP_SUBSTR('2024-07-15', '([0-9]{4})-([0-9]{2})-([0-9]{2})', 1, 1, NULL, 3) AS dy
FROM DUAL;

-- GOOD: Combining multiple REGEXP calls instead of complex unsupported syntax
SELECT employee_id, last_name
FROM employees
WHERE REGEXP_LIKE(last_name, '^[A-Z]')
  AND REGEXP_LIKE(last_name, '[aeiouAEIOU]$');

Fix: Consult the Oracle Regular Expression Support documentation and replace unsupported constructs with Oracle-native alternatives.

Cause 3: Unescaped Metacharacters

Characters like ., ^, $, |, \, (, ), [, ], {, } have special meaning in regex. Forgetting to escape them when you want them treated as literals leads to compile errors or unintended behavior.

-- BAD: Hyphen in wrong position inside character class
SELECT * FROM employees WHERE REGEXP_LIKE(phone_number, '[0-9-+]');
-- May raise ORA-12723 depending on position

-- GOOD: Escape hyphen or place it at start/end of the class
SELECT * FROM employees WHERE REGEXP_LIKE(phone_number, '[0-9+\-]');

-- GOOD: Escape dots in an IP address pattern
SELECT REGEXP_SUBSTR('192.168.0.1', '192\.168\.[0-9]+\.[0-9]+') AS ip FROM DUAL;

-- GOOD: Practical email validation with properly escaped metacharacters
SELECT email
FROM employees
WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}$');

-- Quick escape test
SELECT CASE WHEN REGEXP_LIKE('file.txt', '\.txt$') 
            THEN 'Escaped correctly' 
            ELSE 'No match' END AS result
FROM DUAL;

Fix: When using metacharacters as literals, always prefix them with \ or enclose them in a character class [] where appropriate.

Quick Fix Checklist

Issue	Action
Unbalanced `()` or `[]`	Count and close all brackets
Quantifier at start of pattern	Add a character expression before it
Lookahead / lookbehind	Rewrite using capture groups
Unescaped `.`, `-`, `$` etc.	Add `\` before metacharacters
Pattern copied from Perl/Python	Review against Oracle ERE spec

Prevention Tips

1. Test all patterns against DUAL before deployment.
Always prototype regex patterns in isolation using SELECT ... FROM DUAL or a dedicated test script. Wrap regex logic in a utility function with proper exception handling.

CREATE OR REPLACE FUNCTION safe_regex(p_str VARCHAR2, p_pat VARCHAR2)
RETURN NUMBER AS
BEGIN
  RETURN CASE WHEN REGEXP_LIKE(p_str, p_pat) THEN 1 ELSE 0 END;
EXCEPTION
  WHEN OTHERS THEN RETURN -1; -- signals ORA-12723 or similar
END;
/

2. Maintain a team-level validated pattern library.
Store commonly used and pre-tested patterns (email, phone, date, IP) in a reference table or view. Reuse them across projects to avoid reinventing — and breaking — the wheel.

-- Reusable pattern reference
SELECT * FROM DUAL WHERE REGEXP_LIKE('test@mail.com',
  '^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}$');

Related Oracle Errors

ORA-12725 – Unmatched parentheses in regular expression
ORA-12726 – Unmatched bracket in regular expression
ORA-12727 – Invalid back reference in regular expression
ORA-12728 – Invalid range in regular expression ([z-a])
ORA-12729 – Invalid character class 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.

DEV Community