10g 自定义功能为WM_CONCAT的函数，返回CLOB

最新推荐文章于 2021-04-02 21:12:22 发布

FIGHT_ANGEL

最新推荐文章于 2021-04-02 21:12:22 发布

阅读量1.3k

点赞数

分类专栏： Oracle Function

本文链接：https://blog.csdn.net/FIGHT_ANGEL/article/details/12618159

版权

Oracle Function 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

oracle11g的系统函数wm_concat返回类型为clob，但是10g返回为varchar2，有4000字符限制，如果太长会报如下错误

-- ORA-06502: PL/SQL: 数字或值错误 : character string buffer too small

所以在10g自定义函数，功能与wm_concat类似，但是返回为clob。

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

--类型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;

--方法 F_LINK_LOB

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

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

SELECT F_LINK_LOB(CELL) FROM DS_CHK_RPT_PERF_CELL_G;---用法与wm_concat一样