Oracle Determining the maximum supported size for a regular expression.

Question

I have a regex that ORA-12733 throws, "regex is too long." How to determine the maximum supported size?

FYI: The regular expression is 892 characters. This is a generated regular expression, so I can change the way it is created and executed, but I would like to know what are the restrictions for the maximum size before I change the way it is generated and executed.

(running Oracle 10.2g)

UPDATE:

If it depends on the real regular expression, here is the beginning of it (the rest is repeated the same thing, with different values ​​between ^ and $ ):

(^R_1A$|^R_2A$|^R_3A$|^R_4A$|^R_4B$|^R_5A$|^R_5B$ ...

Answer 1

If you look at the documentation for the regular expression functions, REGEXP_SUBSTR, REGEXP_INSTR and REGEXP_REPLACE, it has the following quote for the template:

pattern is a regular expression. Usually this text is literal and can be any of datatypes CHAR, VARCHAR2, NCHAR or NVARCHAR2. It can contain up to 512 bytes . If the template data type is different from the source_char data type, Oracle Database will convert the template to the source_char data type. To list the operators that you can specify in pattern` **

Taken from here

Answer 2

A figurative regular expression will not need all the anchors at the beginning and end of the line. ^(R_1A|R_2A|R_3A|R_4A|R_4B|R_5A|R_5B)$ will work just as well.

In fact: if the search tokens are really similar, as in the example, you can use it with ^(R_[1-5]A|R_[4-5]B)$ or ^(R_([1-5]A|[4-5]B))$ (for the part of the search string asked in the question).

Checked in 11.2:

 SELECT i, t FROM ( SELECT 1 i, 'R_1A' t FROM DUAL UNION ALL SELECT 2, 'xR_2A' FROM DUAL UNION ALL SELECT 3, 'R_3Ax' FROM DUAL UNION ALL SELECT 4, 'xR_4Ax' FROM DUAL UNION ALL SELECT 5, 'R_4B' FROM DUAL UNION ALL SELECT 6, 'R_5A' FROM DUAL UNION ALL SELECT 7, 'R_5B' FROM DUAL) --WHERE REGEXP_LIKE(t, '(^R_1A$|^R_2A$|^R_3A$|^R_4A$|^R_4B$|^R_5A$|^R_5B$)') --WHERE REGEXP_LIKE(t, '^(R_1A|R_2A|R_3A|R_4A|R_4B|R_5A|R_5B)$') --WHERE REGEXP_LIKE(t, '^(R_[1-5]A|R_[4-5]B)$') WHERE REGEXP_LIKE(t, '^(R_([1-5]A|[4-5]B))$') ORDER BY i;