http://dacoolbaby.iteye.com/blog/1698957
--20180327
重写wm_concat函数，解决行数超过上限问题
/*执行前请将APPS替换为当前登录用户*/
--1.创建自定义类型t_yd_concat
CREATE OR REPLACE TYPE APPS.t_yd_concat
AUTHID CURRENT_USER AS OBJECT
(
CURR_STR clob,
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT t_yd_concat) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT t_yd_concat,
P1 IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN t_yd_concat,
RETURNVALUE OUT clob,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT t_yd_concat,
SCTX2 IN t_yd_concat) RETURN NUMBER
);
/
--2.创建自定义type body
CREATE OR REPLACE TYPE BODY APPS.t_yd_concat
IS
/*创建静态函数ODCIAGGREGATEINITIALIZE*/
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT t_yd_concat)
RETURN NUMBER
IS
BEGIN
SCTX := t_yd_concat(NULL) ;
RETURN ODCICONST.SUCCESS;
END;
/*创建成员函数ODCIAGGREGATEITERATE*/
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT t_yd_concat,
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;
/*
创建成员函数ODCIAGGREGATETERMINATE
*/
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN t_yd_concat,
RETURNVALUE OUT clob,
FLAGS IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURNVALUE := CURR_STR ;
RETURN ODCICONST.SUCCESS;
END;
/*
创建成员函数ODCIAGGREGATEMERGE
*/
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT t_yd_concat,
SCTX2 IN t_yd_concat)
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.建立自定义函数yd_concat
CREATE OR REPLACE FUNCTION APPS.yd_concat(P1 VARCHAR2)
RETURN clob AGGREGATE USING t_yd_concat ;
--4.测试
SELECT yd_concat(P.PROJECT_NAME) FROM SPM_CON_PROJECT P;