ORA-12729: invalid character class in regular expression

Message:
ORA-12729: invalid character class in regular expression

Cause:
An unknown character class was found in the regular expression.

Action:
Ensure a valid characters class is being used.

Explanation and Troubleshooting Tips:
Character classes match any one of a number of characters that fall into a particular category – such as numbers, letters, or white-space.
Regular expressions in Oracle Database follow POSIX standard Extended Regular Expression syntax, and use the operator [: :] to denote character classes.
The standard POSIX character classes supported in all locales are:
[:alnum:] [:cntrl:] [:lower:] [:space:]
[:alpha:] [:digit:] [:print:] [:upper:]
[:blank:] [:graph:] [:punct:] [:xdigit:]

Oracle Database also supports character class shortcuts from Perl regular expressions (for example \s is directly equivalent to [[:space:]]). See Perl-Influenced Extensions in Oracle Regular Expressions for more details.

An ORA-12729 error is raised when the standard POSIX character class syntax is used but the class name is not valid.

SELECT regexp_replace('Tea and coffee', '[[:space:]]', '_') AS "NO_SPACES"
FROM dual;

and
SELECT regexp_replace('Tea and coffee', '\s', '_') AS "NO_SPACES"
FROM dual;

will both output:

NO_SPACES
--------------
Tea_and_coffee

However, if the character class is incorrect:
SELECT regexp_replace('Tea and coffee', '[[:space:]]', '_') AS "NO_SPACES"
FROM dual;

Then the result will be an ORA-12729 error:
Error starting at line 1 in command:
SELECT regexp_replace('Tea and coffee', '[[:spacey:]]', '_') AS "NO_SPACES"
FROM dual
Error report:
SQL Error: ORA-12729: invalid character class in regular expression

The syntax for Regular expressions is strictly case sensitive, so even what appears to be a valid character class will fail if is is not all in the correct case:
Error starting at line 1 in command:
SELECT regexp_replace('Tea and coffee', '[[:Space:]]', '_') AS "NO_SPACES"
FROM dual
Error report:
SQL Error: ORA-12729: invalid character class in regular expression

Posted in Explained, ORA codes, Troubleshooting Tips

Contribute further information: