11G数据库的用户迁移到12C，在老库（Oracle11g）中用户业务查询SQL中有使用到WMSYS.WM_CONCAT()函数，在用户迁移到新库后，业务查询SQL中包含WMSYS.WM_CONCAT()函数的均提示“无效的标识符”错误。通过mos上确认该函数已在12c中废弃。
1、在12c中，我们可以使用listagg函数替代wm_concat函数进行使用。
2、手工创建vm_concat函数
考虑到修改程序的工作量和时间要求，此次我们通过手工创建方式解决,具体操作如下：
(1)解锁wmsys用户
alter user wmsys account unlock; |
GRANT ALL PRIVILEGES TO WMSYS; |
（1）定义类型
CREATE OR REPLACE TYPE WMSYS.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）定义类型body:
CREATE OR REPLACE TYPE BODY WMSYS.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; / |
（3）自定义行变列函数:
CREATE OR REPLACE FUNCTION wmsys.wm_concat(P1 VARCHAR2) RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL ; / |
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 / |
5、还原wmsys用户状态
Alteruser wmsys account lock;
6、测试12cWM_CONCAT函数可以正常使用
