Oracle ORA-12728 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12728: Invalid Range in Regular Expression

ORA-12728 is thrown by Oracle when a regular expression contains an invalid character range inside square brackets, such as [z-a] or [9-0], where the start of the range has a higher code point than the end. This error can appear in any Oracle SQL or PL/SQL context that uses regex functions: REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, and REGEXP_COUNT.

Top 3 Causes

1. Reversed Character Range

The most common cause is simply specifying the range in descending order instead of ascending.

-- ERROR: reversed range
SELECT * FROM employees
WHERE REGEXP_LIKE(last_name, '[z-a]');
-- ORA-12728: invalid range in regular expression

-- FIX: ascending order
SELECT * FROM employees
WHERE REGEXP_LIKE(last_name, '[a-z]');

-- ERROR: reversed numeric range
SELECT REGEXP_REPLACE('ID-9087', '[9-0]', 'X') FROM dual;
-- ORA-12728

-- FIX:
SELECT REGEXP_REPLACE('ID-9087', '[0-9]', 'X') FROM dual;

2. Misplaced Hyphen Creating an Unintended Range

A hyphen (-) inside square brackets acts as a range operator. When placed between two characters where the left has a higher code point than the right, Oracle raises ORA-12728.

-- ERROR: [Z-0] is interpreted as a reversed range
SELECT * FROM products
WHERE REGEXP_LIKE(product_code, '[A-Z-0-9]');
-- ORA-12728 (Oracle sees [Z-0] as invalid)

-- FIX 1: move the hyphen to the end
SELECT * FROM products
WHERE REGEXP_LIKE(product_code, '[A-Z0-9-]');

-- FIX 2: move the hyphen to the beginning
SELECT * FROM products
WHERE REGEXP_LIKE(product_code, '[-A-Z0-9]');

-- FIX 3: escape the hyphen
SELECT * FROM products
WHERE REGEXP_LIKE(product_code, '[A-Z\-0-9]');

3. NLS / Multibyte Character Set Conflicts

In certain NLS environments, character ranges involving non-Latin or multibyte characters can resolve to invalid byte-order ranges.

-- Potentially unsafe in some NLS environments
SELECT * FROM customers
WHERE REGEXP_LIKE(cust_name, '[가-힣]');
-- May raise ORA-12728 depending on NLS_CHARACTERSET

-- SAFE alternative: use POSIX classes
SELECT * FROM employees
WHERE REGEXP_LIKE(last_name, '[[:alpha:]]');

-- Safe numeric check
SELECT * FROM orders
WHERE REGEXP_LIKE(order_id, '^[[:digit:]]+$');

-- Safe alphanumeric check
SELECT * FROM products
WHERE REGEXP_LIKE(sku, '^[[:alnum:]-]+$');

Quick Fix Solutions

Reverse the range — Always write ranges in ascending order: [a-z], [A-Z], [0-9].
Relocate the hyphen — Put - at the start or end of a character class to treat it as a literal: [A-Z0-9-].
Switch to POSIX classes — Replace ad-hoc ranges with POSIX character classes for NLS-safe, readable patterns.

-- Common POSIX classes cheat sheet
-- [[:alpha:]]  → letters (a-z, A-Z)
-- [[:digit:]]  → digits (0-9)
-- [[:alnum:]]  → letters and digits
-- [[:upper:]]  → uppercase letters
-- [[:lower:]]  → lowercase letters
-- [[:space:]]  → whitespace characters
-- [[:punct:]]  → punctuation characters

-- Real-world example: email validation
SELECT email
FROM customers
WHERE REGEXP_LIKE(email,
    '^[[:alnum:]._%-]+@[[:alnum:].-]+\.[[:alpha:]]{2,}$');

Prevention Tips

Validate patterns before deployment — Use a simple wrapper to test regex patterns against dual before running them in production.

-- Quick validation snippet
SELECT CASE
         WHEN REGEXP_LIKE('sample123', '[0-9a-z]') THEN 'Pattern OK'
         ELSE 'No match'
       END AS result
FROM dual;
-- If ORA-12728 fires here, fix the pattern before deploying.

Adopt POSIX classes as your team standard — Add a coding guideline: prefer [[:digit:]] over [0-9] and [[:alpha:]] over [a-z]. POSIX classes eliminate reversed-range mistakes entirely and are independent of NLS settings, making your regex logic portable and maintainable across different database environments.

Related Errors

Error Code	Description
ORA-12725	Unmatched parentheses in regular expression
ORA-12726	Unmatched bracket in regular expression
ORA-12729	Invalid character class in regular expression
ORA-12730	Invalid equivalence class in regular expression
ORA-12731	Invalid collation 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