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