oracle新版无wh_concat函数问题小结

问题描述

Oracle新版本已经停用了wm_concat函数，但是很多程序员在程序中使用了该函数，导致出现错误。为了减轻程序员修改程序的工作量，可以手工创建一个wm_concat函数来临时解决该问题。

oracle新版无wh_concat函数问题小结

问题现象

在执行数据库导入过程中，在编译阶段出现如下错误信息：
alter PACKAGE BODY LISAPP.HAND_MONITOR
compile;
LINE/COL ERROR 319/11
PL/SQL: SQL Statement ignored
319/18 PL/SQL: ORA-00904: “WM_CONCAT”: invalid identifier

解决方法

手工创建wm_concat函数

解锁wmsys用户
alter user wmsys account unlock;
创建包、包体和函数以wmsys用户登录数据库，执行下面的命令：
CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT
-- AUTHID CURRENT_USER
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
) ;
定义类型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;
自定义行变列函数：
CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2) RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL;
创建同义词并授权：
create or replace public synonym WM_CONCAT_IMPL for wmsys.WM_CONCAT_IMPL;
create or replace public synonym wm_concat for wmsys.wm_concat;
grant execute on WM_CONCAT_IMPL to public;
grant execute on wm_concat to public;