wmsys.wm_concat 报错ora-22813的方法

wmsys.wm_concat 报错ora-22813的方法

1.建立自定义type


CREATE OR REPLACE TYPE OQY.t_yd_concat
AUTHID CURRENT_USER AS OBJECT
(
  CURR_STR clob,
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT t_yd_concat) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT t_yd_concat,
  P1 IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN t_yd_concat,
  RETURNVALUE OUT clob,
  FLAGS IN NUMBER)
  RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT t_yd_concat,
  SCTX2 IN t_yd_concat) RETURN NUMBER
);
/


2.建立type body



CREATE OR REPLACE TYPE BODY OQY.t_yd_concat
IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT t_yd_concat)
  RETURN NUMBER
  IS
  BEGIN
  SCTX := t_yd_concat(NULL) ;
  RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT t_yd_concat,

  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 t_yd_concat,
  RETURNVALUE OUT clob,
  FLAGS IN NUMBER)
  RETURN NUMBER
  IS
  BEGIN
  RETURNVALUE := CURR_STR ;
  RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT t_yd_concat,
  SCTX2 IN t_yd_concat)
  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.建立自定义函数yd_concat


CREATE OR REPLACE FUNCTION OQY.yd_concat(P1 VARCHAR2)
RETURN clob AGGREGATE USING t_yd_concat ;
/



4.用yd_concat代替语句中的wmsys.wm_concat