时间戳

首页 > 解决方案 > Oracle 18c - REGEXP_REPLACE 的替代品

regex - Oracle 18c - REGEXP_REPLACE 的替代品

问题描述

迁移到 Oracle 18c 企业版后，无法创建基于函数的索引。

这是我的索引 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
           );

我收到以下错误：

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.

这是 18c 中的已知错误吗？如果不再支持此基于函数的索引，那么编写此函数的另一种方法是什么？

标签： regexoracleindexingddloracle18c

解决方案

问题regexp_replace不是确定性的。更改 NLS 设置时会出现问题：

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 位于英文字母表的末尾。但是在德语中的 U 之后。所以第一个语句不会取代它。第二个可以。

在 Oracle 数据库 12.1 及更早版本regexp_replace中被错误地标记为确定性。12.2 通过使其具有不确定性来解决此问题。

仔细考虑是否有任何变通方法正确管理变音符号。

MOS 说明 2592779.1 进一步讨论了这一点。

推荐阅读