Tuesday, April 24, 2007

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.


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:

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

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:

12   3

we'd have to change the replacement string to
to keep it working like before.

So, what about ORA-12725?

Try to remove the rightmost parenthesis from

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!


Anonymous said...

I really like your blog, and the app, as it shows what can be achieved using apex, and in course we just got the standard example and basics but that didn't really work inspiring.


Byte64 said...

Thanks for your kind words, Floris.
I really love this tool, it's so powerful yet reasonably easy to use compared to other technologies. And it's also incredibly robust and flexible and is going to be even more inspiring with all these APIs that Oracle is making available release after release.
Concerning the built-in demos, they are supposed to show some functionalities and how to properly organize an application rather than solving a specific problem, so probably there is little room for fancy ideas, but you can find many other contributed applications around (see in the OTN forum for example or in the Apex Wiki Galleryand you'll certainly find some outstanding examples of interesting stuff you can do with Apex.

"Don't ask what you can do for Apex, but what Apex can do for you..."

Who said that, William C. Gates i guess? ;-)

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