按照如下横线分开执行,即可:
----------------------------------------------------------------------------------------
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;