RACLE数据库中WM_CONCAT函数失效问题及重建

ORACLE数据库中WM_CONCAT函数失效问题及重建

1、原因

​ WM_CONCAT函数是Oracle的非公开函数，不支持使用该函数，可以使用listagg函数进行替代，Oracle10g之后有些版本已不包含WMSYS.WM_CONCAT函数，若用到此函数， 系统会提示异常：ORA-00904: “WM_CONCAT”: invalid identifier

2、重建

2.1 存在WMSYS用户但无WM_CONCAT处理方法

2.1.1 dba账号登录oracle

sqlplus / as sysdba

2.1.2 解锁wmsys用户

alter user wmsys account unlock;

2.1.3 创建包、包体和函数

CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT
(
  CURR_STR VARCHAR2(32767),
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
                                       P1   IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL,
                                         RETURNVALUE OUT VARCHAR2,
                                         FLAGS       IN NUMBER)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL,
                                     SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER
);

2.1.4 定义类型body

CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL)
    RETURN NUMBER IS
  BEGIN
    SCTX := WM_CONCAT_IMPL(NULL);
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
                                       P1   IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    IF (CURR_STR IS NOT NULL) THEN
      CURR_STR := CURR_STR || ',' || P1;
    ELSE
      CURR_STR := P1;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL,
                                         RETURNVALUE OUT VARCHAR2,
                                         FLAGS       IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURNVALUE := CURR_STR;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL,
                                     SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER IS
  BEGIN
    IF (SCTX2.CURR_STR IS NOT NULL) THEN
      SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
END;

2.1.5 自定义行变列函数

CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2) RETURN VARCHAR2
  AGGREGATE USING WM_CONCAT_IMPL;

2.1.6 创建同义词并赋权

create public synonym WM_CONCAT_IMPL for wmsys.WM_CONCAT_IMPL
/
create public synonym wm_concat for wmsys.wm_concat
/
grant execute on WM_CONCAT_IMPL to public
/
grant execute on wm_concat to public
/

2.2 无WMSYS用户处理方法

2.2.1文件检查

​ 查看数据库服务器上的$ORACLE_HOME/rdbms/admin目录下是否存在owmctab.plb、

owmaggrs.plb、owmaggrb.plb三个文件，如果存在直接进行2.2.2，如果不存在下载owmctab.plb、

owmaggrs.plb、owmaggrb.plb三个文件到数据库服务器上的$ORACLE_HOME/rdbms/admin目录下。

2.2.2执行plb文件

sqlplus / as sysdba
--执行plb文件
@/$ORACLE_HOME/rdbms/admin/owmctab.plb
@/$ORACLE_HOME/rdbms/admin/owmaggrs.plb
@/$ORACLE_HOME/rdbms/admin/owmaggrb.plb
--ORACLE_HOME未配置的可以使用绝对路径进行执行,该路径根据自身实际情况进行修改
@/u01/app/oracle/11.2.0.4/dbhome_1/rdbms/admin/owmctab.plb
@/u01/app/oracle/11.2.0.4/dbhome_1/rdbms/admin/owmaggrs.plb
@/u01/app/oracle/11.2.0.4/dbhome_1/rdbms/admin/owmaggrb.plb