ORACLE 函数处理的数据返回值过长 character string buffer too small .

--问题

-- ORA-06502: PL/SQL: 数字或值错误 : character string buffer too small
--ORA-06512: 在 "WMSYS.WM_CONCAT_IMPL", line 30

?

--错误?? WMSYS.WM_CONCAT（）

?SELECT?? HEAD.BOOKING_HEAD_ID,
?????????? (SELECT?? REPLACE
???????????????????? (
???????????????????????WMSYS.WM_CONCAT(?? CONTAINER.CONTAINER_NO??? --这里改用 F_LINK_LOB（.）?处理?

??????? ?????????????????????????? || '/'
?????????????????????????????????? || SEAL_NO_1
?????????????????????????????????? || '/'
?????????????????????????????????? || CONTAINER_TYPE
?????????????????????????????????? || '/'
?????????????????????????????????? || DECODE (CONTAINER_STATUS_I, 4, 'E', 7, 'L', 'F')),
??????????????????????? ',',
??????????????????????? CHR (10)
???????????????????? )
????????????? FROM?? DOC_BK_CONTAINER CONTAINER
???????????? WHERE?? HEAD.BOOKING_HEAD_ID = CONTAINER.BOOKING_HEAD_ID)
????????????? AS CONTAINERS_BOX_INFO
??? FROM?? DOC_BOOKING_HEAD HEAD
?? WHERE?? HEAD.BOOKING_HEAD_ID = '2c2881d62e50c1c1012e5573b63e54c2'
ORDER BY?? HEAD.BL_NO;

?

? --方法? F_LINK_LOB

CREATE OR REPLACE FUNCTION? F_LINK_LOB (P_STR VARCHAR2)
?? RETURN CLOB
?AGGREGATE USING T_LINK_LOB;

--类型T_LINK_LOB

CREATE OR REPLACE TYPE "T_LINK_LOB"
AS
?? OBJECT
?? (
????? V_LOB CLOB,
????? STATIC FUNCTION ODCIAGGREGATEINITIALIZE (SCTX IN OUT NOCOPY T_LINK_LOB)
????? RETURN NUMBER,
????? MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT NOCOPY T_LINK_LOB, VALUE IN VARCHAR2)
???????? RETURN NUMBER,
????? MEMBER FUNCTION ODCIAGGREGATETERMINATE
????? (
???????? SELF????????? IN??????????? T_LINK_LOB,
???????? RETURNVALUE????? OUT NOCOPY CLOB,
???????? FLAGS???????? IN??????????? NUMBER
????? )
???????? RETURN NUMBER,
????? MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT NOCOPY T_LINK_LOB, CTX2 IN T_LINK_LOB)
???????? RETURN NUMBER
?? )

CREATE OR REPLACE TYPE BODY T_LINK_LOB
IS
?? STATIC FUNCTION ODCIAGGREGATEINITIALIZE (SCTX IN OUT NOCOPY T_LINK_LOB)
????? RETURN NUMBER
?? IS
?? BEGIN
????? SCTX := T_LINK_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 T_LINK_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??????????? T_LINK_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 T_LINK_LOB, CTX2 IN T_LINK_LOB)
????? RETURN NUMBER
?? IS
?? BEGIN
????? NULL;
????? RETURN ODCICONST.SUCCESS;
?? END;
END;