解决Oracle-12C没有wm_concat函数

Oracle-12C中wm_concat函数的替代方案

1. 解锁wmsys用户；

alter user wmsys account unlock;

2. 为wmsys用户授权，可根据需要授权；

grant all privileges to wmsys;

3. 修改wmsys密码；

alter user wmsys identified by 123456;

4. 使用wmsys用户登录,执行创建语句，在wmsys下创建可用的wm_concat函数，直接执行以下语句；

--定义类型
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
);
/
--定义类型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 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;
/