Oracle自定义wmsys.wm_concat避免长度限制网上搜索到这段类似代码但是长度过程还是不行，经过我的修改，亲测可以。

CREATEORREPLACETYPEZH_CONCAT_IM
AUTHIDCURRENT_USERASOBJECT
(
CURR_STRCLOB,
STATICFUNCTIONODCIAGGREGATEINITIALIZE(SCTXINOUTZH_CONCAT_IM)RETURNNUMBER,
MEMBERFUNCTIONODCIAGGREGATEITERATE(SELFINOUTZH_CONCAT_IM,
P1INVARCHAR2)RETURNNUMBER,
MEMBERFUNCTIONODCIAGGREGATETERMINATE(SELFINZH_CONCAT_IM,
RETURNVALUEOUTCLOB,
FLAGSINNUMBER)
RETURNNUMBER,
MEMBERFUNCTIONODCIAGGREGATEMERGE(SELFINOUTZH_CONCAT_IM,
SCTX2INZH_CONCAT_IM)RETURNNUMBER
)
GO

CREATEORREPLACETYPEBODYZH_CONCAT_IM
IS
STATICFUNCTIONODCIAGGREGATEINITIALIZE(SCTXINOUTZH_CONCAT_IM)
RETURNNUMBER
IS
BEGIN
SCTX:=ZH_CONCAT_IM(NULL);
RETURNODCICONST.SUCCESS;
END;
MEMBERFUNCTIONODCIAGGREGATEITERATE(SELFINOUTZH_CONCAT_IM,
P1INVARCHAR2)
RETURNNUMBER
IS
BEGIN
IF(CURR_STRISNOTNULL)THEN
CURR_STR:=CURR_STR||','||P1;
ELSE
CURR_STR:=P1;
ENDIF;
RETURNODCICONST.SUCCESS;
END;
MEMBERFUNCTIONODCIAGGREGATETERMINATE(SELFINZH_CONCAT_IM,
FLAGSINNUMBER)
RETURNNUMBER
IS
BEGIN
RETURNVALUE:=CURR_STR;
RETURNODCICONST.SUCCESS;
END;
MEMBERFUNCTIONODCIAGGREGATEMERGE(SELFINOUTZH_CONCAT_IM,
SCTX2INZH_CONCAT_IM)
RETURNNUMBER
IS
BEGIN
IF(SCTX2.CURR_STRISNOTNULL)THEN
SELF.CURR_STR:=SELF.CURR_STR||','||SCTX2.CURR_STR;
ENDIF;
RETURNODCICONST.SUCCESS;
END;
END;
GO
CREATEORREPLACEFUNCTIONZH_CONCAT(P1VARCHAR2)

RETURNCLOBAGGREGATEUSINGZH_CONCAT_IM;

或则 嫌写自定义函数太麻烦,用xmlagg就可以了,支持返回clob和string类型 selectrtrim(xmlagg(xmlparse(contentl||','wellformed)).getclobval(),',')/*.getstringval()*/from (selectlevelaslfromdualconnectbylevel<=5000)

原文链接：https://www.f2er.com/oracle/212747.html