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