Wednesday, February 20, 2008

ORA-12728: invalid range in regular expression

Always check out the original article at for latest comments, fixes and updates.

This error can be seen when working with regular expressions and character lists.
ORA-12728: invalid range in regular expression
For a live simulation of this error, see Yocoya's Regular Expression Workbench.

While in this case it's easy to see that the character range is incorrect because letter c precedes letter a, whereas it should be written as [a-c], it can be harder to understand when multicharacter elements are present:

for instance, range [a-[.ch.]] is legitimate when NLS_SORT is set to XSPANISH, however range [[.ch.]-[.ll.]] is not.
For further details about NLS_SORT settings, see a previous posting.

Note also that you can put multiple character ranges inside lists as in [0-9a-z], however accented characters will not match.
If you need to include normal letters as well as their accented counterparts, use built-in character classes like [[:alnum:]] or [[:alpha:]].

See message translations for ORA-12728 and search additional resources


Nilesh Jethwa said...

I have been working on an Oracle Apps and general DBA Dashboard. The Dashboard currently has basic information from the information schema.

Would like your opinion to improve and make it useful, for e.g to add DB performance metrics etc, but not sure where this information would be available.

Your opinion is highly regarded and will really appreciate if you could point me to some resources on this.
( njethwa @ gma!l . com)

Byte64 said...

Thanks for asking me Nilesh, however i don't feel entitled to give away generic advices in this area, there are people out there who are much more enthusiastic about the *real* DBA work than me.
I mean, i don't feel to be a particularly gifted DBA, certainly i prefer developing new applications, i wear the DBA hat only when necessary ;-)

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles