Oracle 18c - Alternative to REGEXP_REPLACE

Question

After migrating to Oracle 18c Enterprise Edition, a function based index fails to create.

Here is my index DDL:

CREATE INDEX my_index ON my_table
(UPPER( REGEXP_REPLACE ("DEPT_NUM",'[^[:alnum:]]',NULL,1,0)))
TABLESPACE my_tbspace
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

I get the following error:

ORA-01743: only pure functions can be indexed
01743. 00000 -  "only pure functions can be indexed"
*Cause:    The indexed function uses SYSDATE or the user environment.
*Action:   PL/SQL functions must be pure (RNDS, RNPS, WNDS, WNPS).  SQL
           expressions must not use SYSDATE, USER, USERENV(), or anything
           else dependent on the session state.  NLS-dependent functions
           are OK.

Is this a known bug in 18c? If this function based index is no longer supported, what is another way to write this function?

Answer 1

The issue is regexp_replace is not deterministic. The problem arises when changing NLS settings:

alter session set nls_language = english;

with rws as (
  select 'STÜFF' v
  from   dual
)
  select regexp_replace ( v, '[A-Z]+', '#' )
  from   rws;

REGEXP_REPLACE(V,'[A-Z]+','#')   
#Ü#  

alter session set nls_language = german;

with rws as (
  select 'STÜFF' v
  from   dual
)
  select regexp_replace ( v, '[A-Z]+', '#' )
  from   rws;

REGEXP_REPLACE(V,'[A-Z]+','#')   
#     

U-umlaut is at the end of the alphabet in English. But after U in German. So the first statement doesn't replace it. The second does.

In Oracle Database 12.1 and earlier regexp_replace was incorrectly marked as deterministic. 12.2 fixed this by making it non-deterministic.

Consider carefully whether any workarounds manage diacritics correctly.

MOS note 2592779.1 discusses this further.

Answer 2

Most likely the REGEXP_REPLACE causes the problem, see Find out if a string contains only ASCII characters. You can bypass the limitation with a user defined function (thanks to Bob Jarvis)

CREATE OR REPLACE FUNCTION KEEP_ALNUM(strIn IN VARCHAR2)
  RETURN VARCHAR2
  DETERMINISTIC
AS
BEGIN
  RETURN UPPER(REGEXP_REPLACE(strIn, '[^[:alnum:]]', NULL, 1, 0));
END KEEP_ALNUM;
/

CREATE INDEX DEPTS_1 ON DEPTS(KEEP_ALNUM(DEPT_NUM));

Just ensure function has keyword DETERMINISTIC, then you can define even useless functions like below and create a functional index on it

CREATE OR REPLACE FUNCTION SillyValue RETURN VARCHAR2 DETERMINISTIC
AS
BEGIN
  RETURN DBMS_RANDOM.STRING('p', 20);
END;
/