在工作中遇到行转列的问题

今天在做查询时使用了WMSYS.WM_CONCAT函数，但是进行几次外连接后就报错ora-22922 CLOB错误。

主要是使用WMSYS.WM_CONCAT函数，它默认返回值是CLOB类型的，再次和其他表进行连接时就会报CLOB的错误。

从网上自己找了一个行专列的函数，是这样的：

1. CREATE OR REPLACE TYPE ZH_CONCAT_IM

AUTHID CURRENT_USER AS OBJECT

(

  CURR_STR VARCHAR2(32767),

  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT ZH_CONCAT_IM) RETURN NUMBER,

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT ZH_CONCAT_IM,

               P1 IN VARCHAR2) RETURN NUMBER,

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN ZH_CONCAT_IM,

                                 RETURNVALUE OUT VARCHAR2,

                                 FLAGS IN NUMBER)

                     RETURN NUMBER,

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT ZH_CONCAT_IM,

                    SCTX2 IN  ZH_CONCAT_IM) RETURN NUMBER

);

2. CREATE OR REPLACE TYPE BODY ZH_CONCAT_IM

IS

  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT ZH_CONCAT_IM)

  RETURN NUMBER

  IS

  BEGIN

    SCTX := ZH_CONCAT_IM(NULL) ;

    RETURN ODCICONST.SUCCESS;

  END;

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT ZH_CONCAT_IM,

          P1 IN VARCHAR2)

  RETURN NUMBER

  IS

  BEGIN

    IF(CURR_STR IS NOT NULL) THEN

      CURR_STR := CURR_STR || ':' || P1;

    ELSE

      CURR_STR := P1;

    END IF;

    RETURN ODCICONST.SUCCESS;

  END;

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN ZH_CONCAT_IM,

                                 RETURNVALUE OUT VARCHAR2,

                                 FLAGS IN NUMBER)

    RETURN NUMBER

  IS

  BEGIN

    RETURNVALUE := CURR_STR ;

    RETURN ODCICONST.SUCCESS;

  END;

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT ZH_CONCAT_IM,

                                   SCTX2 IN ZH_CONCAT_IM)

  RETURN NUMBER

  IS

  BEGIN

    IF(SCTX2.CURR_STR IS NOT NULL) THEN

      SELF.CURR_STR := SELF.CURR_STR || ':' || SCTX2.CURR_STR ;

    END IF;

    RETURN ODCICONST.SUCCESS;

  END;

END;

 

3.

CREATE OR REPLACE FUNCTION ZH_CONCAT(P1 VARCHAR2)

RETURN VARCHAR2 AGGREGATE USING ZH_CONCAT_IM ;