sql – What is the alternative to REGEXP_REPLACE to create a functional index?


After migrating to Oracle 18c Enterprise Edition, functional index (FBI) creation fails.

This is how the DDL for creating an index looks like:

CREATE INDEX my_index ON my_table(
    UPPER (REGEXP_REPLACE ("DEPT_NUM", '[^[:alnum:]]', NULL, 1, 0)));

I am getting 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 such functionality is no longer supported for FBI, what other way is there to provide such functionality for the index?


Prior to the release, it was possible to create a functional index (FBI) with functions whose result was non-deterministic.

For example, in it looked like it worked:

create table depts (dept_num) as
    select cast (column_value as varchar2 (16)) dept_num
    from xmlTable ('"12345","ABC123", "XYZ#123"');
create index depts_idx on depts(
     upper (regexp_replace ((dept_num), '[^[:alnum:]]', null, 1, 0)));

Index DEPTS_IDX created.

The same in will end with an error when creating the index:

ORA-01743: only pure functions can be indexed

As for user-defined functions, everything is clear here, both in and it is explicitly stated that:

Any user-defined function referenced in column_expression must be declared as DETERMINISTIC .

In the case of the REGEXP_REPLACE internal SQL function, Oracle has recognized that its use in FBI is a bug and has fixed it since release Read more @ConnorMcDonald on Ask Tom :

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

Indeed, the result of this function depends on the NLS settings of the session, i.e. non-deterministic. For example, the result of the same query with umlauts will be different:

alter session set nls_language = 'german';  --> null
alter session set nls_language = 'english'; --> ÄÖÜ

select (regexp_replace ('ABCÄÖÜ','[A-Z]*')) res from dual;

One possible solution is to restrict department IDs to ASCII characters only. In this case, it is enough to wrap the logic in a user-defined function with a deterministic result. For example like this:

create or replace function normalizeDeptNum (deptNum varchar2) return varchar deterministic is
    if replace (asciistr (deptNum), asciistr (chr (92)), chr (92)) != deptNum then
        raise_application_error (
            -20000, 'The value in deptNum contains non-ascii char >'||deptNum||'<');
    end if;
    return upper (regexp_replace (deptNum, '[^[:alnum:]]', null, 1, 0));
create index depts_idx on depts (normalizeDeptNum (dept_num));

Index DEPTS_IDX created.

insert into depts (dept_num) values ('AAA//123');

1 row inserted.

insert into depts (dept_num) values ('ÄÄÄ//123');

ORA-20000: The value in deptNum contains non-ascii char >ÄÄÄ//123<
Scroll to Top