今天在做查询时使用了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 ;