oracle wm_concat 空,oracle重新wm_concat函数，字段不可为空（主要解决10g以上版本没有wm_concat函数的问题）...

按照如下横线分开执行，即可：

----------------------------------------------------------------------------------------

CREATE OR REPLACE TYPE wm_concat_LOB AS OBJECT (

V_LOB CLOB,

STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT NOCOPY wm_concat_LOB) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT NOCOPY wm_concat_LOB, VALUE IN VARCHAR2) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN wm_concat_LOB, RETURNVALUE OUT NOCOPY CLOB, FLAGS IN NUMBER) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT NOCOPY wm_concat_LOB, CTX2 IN wm_concat_LOB) RETURN NUMBER

)

-------------------------------------------------------------------------------------

CREATE OR REPLACE TYPE BODY wm_concat_LOB IS

STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT NOCOPY wm_concat_LOB) RETURN NUMBER IS

BEGIN

SCTX := wm_concat_LOB(NULL);

DBMS_LOB.CREATETEMPORARY(SCTX.V_LOB, TRUE, DBMS_LOB.SESSION);

DBMS_LOB.OPEN(SCTX.V_LOB, DBMS_LOB.LOB_READWRITE);

RETURN ODCICONST.SUCCESS;

END;

MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT NOCOPY wm_concat_LOB, VALUE IN VARCHAR2) RETURN NUMBER IS

BEGIN

DBMS_LOB.WRITEAPPEND(SELF.V_LOB, LENGTH(VALUE) + 1, VALUE || ',');

RETURN ODCICONST.SUCCESS;

END;

MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN wm_concat_LOB, RETURNVALUE OUT NOCOPY CLOB, FLAGS IN NUMBER) RETURN NUMBER IS

BEGIN

DBMS_LOB.CREATETEMPORARY(RETURNVALUE, TRUE, DBMS_LOB.CALL);

DBMS_LOB.COPY(RETURNVALUE, SELF.V_LOB, DBMS_LOB.GETLENGTH(SELF.V_LOB) - 1);

RETURN ODCICONST.SUCCESS;

END;

MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT NOCOPY wm_concat_LOB, CTX2 IN wm_concat_LOB) RETURN NUMBER IS

BEGIN

NULL;

RETURN ODCICONST.SUCCESS;

END;

END;

--------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION wm_concat_CLOB(P_STR VARCHAR2) RETURN CLOB

AGGREGATE USING wm_concat_LOB;

-----------------------------------------------------------------------------

select * from sys_user

-- SET AUTOT TRACE STAT

SELECT  wm_concat(distinct username),wm_concat_CLOB(distinct username) FROM sys_user --WHERE ROWNUM < 10000;