Skip to Main Content
  • Questions
  • Adding constraint with REGEXP_REPLACE fails

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Kay.

Asked: March 23, 2018 - 1:29 pm UTC

Last updated: March 21, 2024 - 12:39 am UTC

Version: 12.2 and 18

Viewed 10K+ times! This question is

You Asked

Hi Tom,
Why is this script failing?
There is no errors on running this in Oracle 12.01 and previous versions!

with LiveSQL Test Case:

and Connor said...

You have been relying on a bug - which we've now fixed.


Bug 20804063  ORA-1499 as REGEXP_REPLACE is allowed to be used in Function-based indexes (FBI)


Symptoms:
Related To:
Corruption (Index)
Error May Occur
ORA-1499 / ORA-8102
Function Based Index (Including DESC Indexes)
Virtual Columns
REGEXP_REPLACE
CREATE TABLE
CREATE INDEX
Description
This fix checks if function REGEXP_REPLACE is used by CREATE INDEX, CREATE TABLE or CONSTRAINT and avoids 
 the DDL by producing an error for each case.
 
Function REGEXP_REPLACE() could be allowed to be used in functional indexes, virtual columns and
 check constraints; although it is not Deterministic.
 
This then may cause table index/inconsistency with errors like:
 
ORA-1499 by analyze table validate structure cascade
ORA-8102 by UPDATE/DELETE statements
 
Rediscovery Notes
 If you are able to create a functional index, virtual column or check constraint using REGEXP_REPLACE() 
 then you are hitting this bug.
 
 This fix checks if function REGEXP_REPLACE is used by CREATE INDEX, CREATE TABLE or CONSTRAINT and avoids 
 the DDL by producing an error for each case.
 
Errors examples after installing this fix:
 
CREATE INDEX:
 
  SQL> create index pat_x on pat (regexp_replace(upper(b), '[A-Z0-9]',NULL))
                             *
  ERROR at line 1:
  ORA-01743: only pure functions can be indexed
 
CREATE TABLE:
 
  SQL> create table pat
    2   (a number, b varchar2(10),
    3    c generated always
    4       as (regexp_replace(upper(b), '[A-Z0-9]',NULL)) virtual)
    5  partition by range (c)
    6  (partition p1 values less than ('G'),
    7   partition p2 values less than (maxvalue));
       as (regexp_replace(upper(b), '[A-Z0-9]',NULL)) virtual)
         *
ERROR at line 4:
ORA-54002: only pure functions can be specified in a virtual column expression 
 
CONSTRAINT:
 
  SQL> alter table pat2 add constraint pat2c
    2   check (regexp_replace(upper(b), '[A-Z0-9]',NULL) is not null);
   check (regexp_replace(upper(b), '[A-Z0-9]',NULL) is not null)
          *
  ERROR at line 2:
  ORA-02436: date or system variable wrongly specified in CHECK constraint


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

using regexp_replace

Rajeshwaran, Jeyabal, March 26, 2018 - 7:30 am UTC

demo@ORA12C> alter table persons
  2  add constraint person_check_city
  3  check( regexp_replace( zip,'\d+') is null );
check( regexp_replace( zip,'\d+') is null )
       *
ERROR at line 3:
ORA-02436: date or system variable wrongly specified in CHECK constraint


demo@ORA12C> alter table persons
  2  add constraint person_check_city
  3  check( replace( translate(zip,'0123456789','1'),'1' ) is null );

Table altered.

demo@ORA12C> insert into persons(zip) values(55);

1 row created.

demo@ORA12C> insert into persons(zip) values('55E');
insert into persons(zip) values('55E')
*
ERROR at line 1:
ORA-02290: check constraint (DEMO.PERSON_CHECK_CITY) violated


demo@ORA12C>


Questions:
1) If regexp_replace is not going to work, then do we need to stick with translate and replace trick?

2) so how to handle the zip to accept only number using REGEXP from 12.2 and above? one way is using REGEXP_LIKE, not possible using regexp_replace?

demo@ORA12C> alter table persons
  2  drop constraint person_check_city;

Table altered.

demo@ORA12C>
demo@ORA12C> alter table persons
  2  add constraint person_check_city
  3  check ( regexp_like (zip,'^\d+$') ) ;

Table altered.

demo@ORA12C> insert into persons(zip) values(55);

1 row created.

demo@ORA12C> insert into persons(zip) values('55E');
insert into persons(zip) values('55E')
*
ERROR at line 1:
ORA-02290: check constraint (DEMO.PERSON_CHECK_CITY) violated


demo@ORA12C>



Connor McDonald
March 30, 2018 - 5:00 am UTC

I'd be inclined to stay away from REGEXP_LIKE as well.

The issue is related to NLS. If you run this:

alter session set nls_language = 'german';
select regexp_replace('A GERMAN OH Ö','[A-Z]*') from dual;
alter session set nls_language = 'english';
select regexp_replace('A GERMAN OH Ö','[A-Z]*') from dual;

you get different results because "umlaut O" sits between A and Z *dependent* on your language settings. So REGEX functions are senstive to NLS.

What does the Oracle documentation say ?

Iudith Mentzel, April 19, 2018 - 9:09 am UTC

Hello Connor, All,

I found this thread following your today Asktom session, which was excellent as always :)

Checking the 12.2 SQL documentation on CHECK constraints,

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/constraint.html#GUID-1055EA97-BA6F-4764-A15F-1024FD5B6DFE

it contains the following sentence:

"
If the condition of a check constraint depends on NLS parameters, such as
NLS_DATE_FORMAT, Oracle evaluates the condition using the database values of the
parameters, not the session values. You can find the database values of the NLS
parameters in the data dictionary view NLS_DATABASE_PARAMETERS. These values
are associated with a database by the DDL statement CREATE DATABASE and never
change afterwards.
"


If Oracle was indeed following this rule, then isn't this supposed to avoid
the ORA-02436 error ?


Or, alternatively, maybe in a future version Oracle could add an additional argument to the REGEXP functions, for specifying NLS parameter values explicitly, like in other SQL functions ?


Thanks a lot & Best Regards,
Iudith Mentzel

Connor McDonald
April 20, 2018 - 2:55 am UTC

My understanding is that check constraints are "caught in the crossfire".

You *could* use regexp_exp in a function-based index....which creates the problem (index versus table data). The resolution to that issue is to mark regexp_... as non-deterministic.

The check constraint then simply says: "This function is non deterministic"

livesql link not working

Rajeshwaran, Jeyabal, June 23, 2018 - 10:18 am UTC

Team,

any reason why the above livesql link is not working?

https://livesql.oracle.com/apex/livesql/file/content_GF347SDWNX8MXSBHW5A1V6OEA.html
Connor McDonald
June 25, 2018 - 9:17 am UTC

I've asked the livesql team for some info.

I'll update when I hear back

How to best deal with this bug?

Bernhard Schwarz, August 01, 2018 - 3:39 am UTC

So what is the recommendation on how to best deal with this bug?
So far I haven't found any advice from Oracle support answering those questions.

Should you right away get rid of all REGEXP functions used in virtual columns (VC), function based index (FBI) and check constraints (CC) in any pre 12.2 DB?
Should you try to use other functions like TRANSLATE and REPLACE instead to achieve the same functionality?

What should you do in cases where you cannot achieve the same functionality without using REGEXP functions?
I guess it is still possible to use REGEXP function in Views even in 12.2, but then you are facing unwanted non-deterministic, i.e. NLS dependent behaviour as well.
Is it a good idea to just no longer use problematic ranges like a-z as REGEXP patterns and instead just list the whole range explicitly like abcdefghijklmnopqrstuvwxyz ? Does this for sure prevent REGEXP functions becoming non-deterministic or are there other reasons as well that could make them non-deterministic?

Will the fix of this bug in 12.2 prevent you from upgrading to 12.2 or will this bug fix just prevent new DDL using REGEXP for VC, FBI or CC to be executed, but leave pre-existing objects untouched which are violating against that?
Connor McDonald
August 20, 2018 - 4:11 am UTC

So what is the recommendation on how to best deal with this bug?

Yeah, the current situation is not ideal. For me, I'd be opting to use workarounds where possible (eg translate, replace etc)

If this is not possible, then the constraint can be implemented with trigger code, where if absolutely required, you could change session to a fixed/known NLS before performing the regexp check. Not great I know.

I guess it is still possible to use REGEXP function in Views even in 12.2, but then you are facing unwanted non-deterministic, i.e. NLS dependent behaviour as well.


Agreed.

Is it a good idea to just no longer use problematic ranges like a-z as REGEXP patterns and instead just list the whole range explicitly like abcdefghijklmnopqrstuvwxyz ? Does this for sure prevent REGEXP functions becoming non-deterministic or are there other reasons as well that could make them non-deterministic?

I think this can be taken in a case by case basis. Obviously the perfect resolution is to accept NLS parameters into the functions themselves (like to_char etc).

Will the fix of this bug in 12.2 prevent you from upgrading to 12.2 or will this bug fix just prevent new DDL using REGEXP for VC, FBI or CC to be executed, but leave pre-existing objects untouched which are violating against that?

That's something I have not tried. I'd be very surprised if we are going and checking all existing constraints on the upgrade. My hypothesis here is that we'll raise an error on first usage, but that is unverified.

I hope to try that sometime this week and blog about it

Bug in 12.2

Mansi, March 19, 2024 - 2:14 pm UTC

My oracle version is "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0"

I could see the already created FBI using regexp_replace is as it is in one of the database.

Now that if I am trying to create FBI using regexp_replace is giving below error.

ORA-01743: only pure functions can be indexed
Connor McDonald
March 21, 2024 - 12:39 am UTC

Here's an old video of mine (excuse the terrible audio) that explains this


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library