ORA-12728: invalid range in regular expression

Always check out the original article at http://www.oraclequirks.com 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

ORA-12731: invalid collation class in regular expression

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This error can be seen when working with regular expressions and character lists containing collation classes.

ORA-12731: invalid collation class in regular expression

For a live simulation of this error, see Yocoya's Regular Expression Workbench.

In order to understand how to fix the problem in a generic situation, let me show you first how to fix it in the workbench, where you have to change the NLS_SORT option, displayed in the sidebar, to the value XSPANISH (extended Spanish).

Acceptable characters within a collation class are determined by the value of initialization parameter NLS_SORT, that can be altered at the session level with:

ALTER SESSION SET NLS_SORT=XSPANISH;

For an in-depth discussion of linguistic sorting, see the relevant chapter of the Database Globalization Support Guide (10g).

So, when you switch the linguistic option in the workbench picking a value from the drop down list, you are causing the program to alter the current session value of NLS_SORT.

However, if after changing NLS_SORT you are still experiencing the same the problem, it could be that you have specified an invalid multicharacter element, in which case you must determine what characters are allowed by Oracle for the desired linguistic sort option.
For instance, in extended Spanish only "ch" and "ll" are allowed as multicharacter elements in collation classes.

But what is the purpose of multicharacter collation elements anyway?

As you can see in workbench live example, i defined a character range within a list, where all characters between a and ch will be replaced by an asterisk. Being considered a single character, ch will be replaced by a single asterisk, not two.
The collation element allows you to define a range of characters according to linguistic rules, where the starting or ending element can be a multicharacter element.

See message translations for ORA-12731 and search additional resources

ORA-12729: invalid character class in regular expression

I was creating a regular expression using the Regular Expression Workbench for Oracle for checking if an URL was a well formed Oracle Apex URL, when i hit this error:

ORA-12729: invalid character class in regular expression

The error was easily explained: instead of writing [:digit:], i wrote [:digits:].

Here is the complete list of valid character classes:

[:alnum:] All alphanumeric characters
[:alpha:] All alphabetic characters
[:blank:] All blank space characters.
[:cntrl:] All control characters (nonprinting)
[:digit:] All numeric digits
[:graph:] All [:punct:], [:upper:], [:lower:], and [:digit:] characters.
[:lower:] All lowercase alphabetic characters
[:print:] All printable characters
[:punct:] All punctuation characters
[:space:] All space characters (nonprinting)
[:upper:] All uppercase alphabetic characters
[:xdigit:] All valid hexadecimal characters

You can find the Apex URL pattern in the library of available formats, by checking the format radio button.

See message translations for ORA-12729 and search additional resources.

---

ORA-12729: classe caratteri non valida nell'espressione regolare
ORA-12729: clase de carácter no válida en expresión normal
ORA-12729: classe de caràcter no vàlida a l'expressió regular
ORA-12729: classe de caractères non valide dans l'expression régulière
ORA-12729: Ungültige Zeichenklasse in regulärem Ausdruck
ORA-12729: μη αποδεκτή κλάση χαρακτήρων σε τυπική έκφραση
ORA-12729: ugyldig tegnklasse i almindeligt udtryk
ORA-12729: ogiltig teckenklass i reguljärt uttryck
ORA-12729: ugyldig tegnklasse i det regulære uttrykket
ORA-12729: virheellinen merkkiluokka vakiolausekkeessa
ORA-12729: érvénytelen karakterosztály a reguláris kifejezésben
ORA-12729: clasă de caractere nevalidă în expresia logică
ORA-12729: ongeldige tekenklasse in reguliere uitdrukking
ORA-12729: classe de caractere inválida na expressão comum
ORA-12729: classe de caracteres inválida na expressão regular
ORA-12729: недопустимый класс символа в регулярном выражении
ORA-12729: neplatná třída znaků v regulérním výrazu
ORA-12729: neplatná znaková trieda v regulárnom výraze
ORA-12729: niepoprawna klasa znaku w wyrażeniu regularnym
ORA-12729: düzenli ifadede geçersiz karakter sınıfı
ORA-12729: invalid character class in regular expression

ORA-21779, the final revenge?

Yes, it's the third time i have to talk about this awful, elusive, erratic, annoying oracle error.

But there are chances that we are finally approaching the end of this nightmare.

I was testing a new, top-secret ;-) function that uses REGEXP_REPLACE as part of his logic and is called several times within a report region of an Oracle Application Express page and almost immediately i began hitting this haunting problem.

ORA-21779: Duration not active

I found myself screaming: "NO! NOT AGAIN!".

And this time it wasn't occurring on my Windows machine but on a full fledged Solaris server.

So, i contacted my friend John Scott who was fairly surprised to hear about that and together we immediately set out in pursuit of a solution.

The worse thing about this error is in that it's an intermittent one.
It may appear three times in a row then it can disappear for two times, then once again and so on, without any meaningful pattern.

At any rate, after a huge amount of try-this and try-that, John had an excellent intuition:

"It could have to do with the session character set".

He based this assumption on the fact that he experienced the problem only when he was looking at the page in the Italian version, but apparently the error didn't occur when the page was in English.

Given the fact that Apex, as part of its globalization logic, alters some sessions parameters depending on the current language setting, whose detection method is defined in the application attributes, it is perfectly possible that, by doing so, it's affecting the way REGEXP_REPLACE works when it is embedded in an Apex report.

With this theory in mind, i initially created two page processes, one occurring Before Regions and the other one occurring After Regions.
The former saves the current NLS_LANGUAGE into an application item and temporarily sets NLS_LANGUAGE='AMERICAN', the latter sets NLS_LANGUAGE back to its previous value.

bug fix part#1 (to be run right before the report):

begin
select value
into :SAVED_NLS_LANGUAGE
from nls_session_parameters
where parameter = 'NLS_LANGUAGE';

execute immediate 'ALTER SESSION SET NLS_LANGUAGE=''AMERICAN''';
end;

bug fix part #2 (to be run right after the report)

begin
execute immediate 'ALTER SESSION SET NLS_LANGUAGE='
|| chr(39) || :SAVED_NLS_LANGUAGE || chr(39);
end;

And you know what?
ORA-21779 is gone!
Since i implemented the ALTER SESSION fix, i haven't seen this error any more.

At this point i started refining the bug fix, because i didn't want to run these PL/SQL snippets before and after each and every region, but just before and after the offending one, so i converted the page processes into two special PL/SQL based page regions, surrounding the main report region.
And the report works fine.

Although we apparently managed to get rid of the problem, we couldn't precisely identify the root cause. The attempts to reproduce the error in a simpler scenario have failed, so it's not just a matter of setting NLS_LANGUAGE='ITALIAN' and call REGEXP_REPLACE, there must be something else going on behind the scene.

I am rather sure that it's a combination of factors:

1. the use of REGEXP_REPLACE;
2. the NLS_LANGUAGE setting;
3. the function call to REGEXP_REPLACE inside a report region, as part of an iterative process where a CLOB object is eventually returned perhaps?
4. some other component we don't even suspect of?
   

The bad aspect with bugs like this is in that it makes really hard to submit a TAR request because of the complex setup required to reproduce the problem and without a minimal set of steps showing the problem, there are few chances to draw the attention of Oracle Support and get it fixed.

See message translations for ORA-21779 and search additional resources.

ORA-12725: unmatched parentheses in regular expression

Lately i am working a lot with regular expressions.
At first the syntax of regular expressions is rather tough to remember, but after a while a whole world of possibilities opens up to the developer.

One of the most useful features of Oracle's implementation of regular expressions is the support for backreference expressions, sorts of variables holding the values of matching subexpressions delimited by round parentheses "(...)".

While backreference expressions can appear in the regular expression pattern of any REGEXP function and also in the replacement parameter of REGEXP_REPLACE, the example given below applies only to the latter case.

Now you might ask: what's so cool about this backreference expression?
Well, backreference expressions allow you to move around bits of text in an easy way.

Example:

I just registered for the upcoming ODTUG Kaleidoscope Apex Training in Daytona Beach, and i came across a screen where different registration fees were proposed.

Imagine that this information must be stored in a database in a different format, with the currency symbol to the right.

We can do this operation with the help of a regular expressions and function REGEXP_REPLACE.

In the frame below a fully functional Yocoya's Regular Expression Workbench is encapsulated, you can follow the instructions given below and interact with the tool, without leaving the blog.



Then, in the input box containing "put the pattern here" cut and paste:

(\$)([[:digit:]]{1,3})

and in the input box containing "put the replacement here", cut and paste:

\2\1

then click APPLY.

The test report should now display the prices with currency postfix notation.

Backreference expressions must be in the range 1-9 and, as far as i know, you can tell which number refers to which expression by counting the left open parentheses "(" starting from the leftmost position in the pattern, so if we take the following slightly modified expression where i added one more pair of parentheses around it:

((\$)([[:digit:]]{1,3}))
12   3

we'd have to change the replacement string to

\3\2

to keep it working like before.


So, what about ORA-12725?

Try to remove the rightmost parenthesis from

((\$)([[:digit:]]{1,3}))

you'll see that the following error is reported:

ORA-12725: unmatched parentheses in regular expression

This error will be raised only when a round parenthesis is missing, a missing square bracket "[" or "]" would return instead:

ORA-12726: unmatched bracket in regular expression

See you at the ODTUG Kaleidoscope 2007!

Regular Expressions

Would you mind to give me some feedback on this tool?



It's an online regular expression workbench, it's just a beta version, but it's almost (or so i presume...) fully functional.

It's powered by Oracle Application Express and it's my first multilingual application.
By default it will display in italian, but a preliminary english translation is available, including all the help windows, and you can switch language by clicking on the appropriate link in the lower left corner.

I used it myself for developing and testing some regular expressions and i found it's pretty neat.
Of course there is room for improvement and that is why i'd really like to hear from you.

A spanish translation is underway and more languages will be added later on.

This online environment is also an excellent way for showing you why you get certain errors like:

ORA-12726 unmatched bracket in regular expression

or how Oracle regular expressions work compared to other implementations.

So, let's start playin' with the toy, it's free.

ORA-21779 reloaded

I am getting no more ORA-21779 lately.
I am wondering if patch 10.1.0.5 did work in the end for my regexp_replace problem.
Definitely a weird case.

UPDATED July 13, 2007
if you are getting this error in an Oracle Application Express page, try out this workaround.

---

ORA-21779: durata non attiva
ORA-21779: duración no activa
ORA-21779: durada inactiva
ORA-21779: durée dépassée
ORA-21779: Dauer nicht aktiv
ORA-21779: διάρκεια μη ενεργή
ORA-21779: varighed ikke aktiv
ORA-21779: varaktigheten är inte aktiv
ORA-21779: varighet ikke aktiv
ORA-21779: kesto ei aktiivinen
ORA-21779: az időtartam nem érvényes
ORA-21779: durata nu este activă
ORA-21779: Tijdsduur is niet actief.
ORA-21779: duração não está ativa
ORA-21779: duração não activa
ORA-21779: период не активный
ORA-21779: trvání není aktivní
ORA-21779: trvanie nie je aktívne
ORA-21779: nie uaktywniono czasu trwania
ORA-21779: süre etkin değil

ORA-21779 the brain teaser

Amongst my favourite oracle 10G enhancements there is the support for regular expressions.
There is a whole bunch of functions to deal with strings using these powerful pattern matching descriptors so i decided to use them for implementing a procedure that unscrambles search engine URLs in pursuit of the search keywords that led a user to my site.

So, i set out and wrote this wonderful web search URL decoder for the most popular engines like Google and Yahoo and installed it on my development box, just to find out minutes later that, at times, i was getting a strange error called ORA-21779: Duration not active.

The short error description wasn't very helpful, what kind of duration is it talking about?!?
Don't panic, let's see first what the Error Messages manual for version 10.1 says about it.

Cause: User is trying to use a duration that has been terminated
Action: User should avoid performing such operation.

I don't know the rest of you guys, but i'd love to know the "backstage" of the birth of this message. I get this fancy picture in my mind of two tired oracle techies working late in an office where everyone else is at home and sleeping and one screams at the colleague sitting at the other end of a big open space room:

- JOE! What was that error you got this morning?
- ...ion not active! (very weak)
- What?
- ...ion NOT ACTIVE! (somewhat louder)
- HUH?!?
- &$%!!
- (long silence)... and how do you fix it?
- Just don't do it!
- (long silence)

At any rate, back to my problem, it took me a while to figure out that the error was raised by function regexp_replace, for two reasons:

#1 the error was not returned every time i executed the query even against the same inputs.
#2 the function is contained in a so-called "process" inside a page definition of Oracle Apex (Application Express 2.0) , so, at first i thought it was returned by the underlying query of the report being displayed, not by the after submit process.

So, in the end, i commented out the function call and the error stopped occurring.
Said that, i turned up to oracle metalink in search of more descriptive explanations of the problem but i found just one note mentioning regexp functions called inside "pipelined functions".
I also found another note (or was it the same?) suggesting that patch 10.1.0.5 could fix the problem, so i installed the patch too, with little success regarding the point, but introducing the ORA-12640 problem i related in a previous post.
It was not my lucky day, definitely.

After several attempts to isolate the real cause of the problem however, i found out that the error can happen even without nesting regexp_replace inside pipeline functions, but still i cannot reproduce this behaviour sistematically, there is some erraticism in it.
To add more to the picture, this seems to be a Windows only problem.
I could not manage to reproduce it on Solaris, which is good on the other hand because it's my target production environment.
I also left a message in the metalink forum, but nobody has replied up to date.

So, at the end of the day, the function in my development box is still haunted by these infrequent ORA-21779 errors and i don't dare to install the new functionality in a production environment even if, so far, all the tests have returned positive results.

Any suggestions?

PS: UPDATED July 13, 2007
if you are getting this error in an Oracle Application Express page, try out this workaround.