解决11g r2,12c使用wm_concat报错问题

创建type

 1 CREATE OR REPLACE TYPE zh_concat_im
 2 AUTHID CURRENT_USER AS OBJECT
 3 (
 4 CURR_STR VARCHAR2(32767),
 5 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im) RETURN NUMBER,
 6 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,
 7 P1 IN VARCHAR2) RETURN NUMBER,
 8 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,
 9 RETURNVALUE OUT VARCHAR2,
10 FLAGS IN NUMBER)
11 RETURN NUMBER,
12 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,
13 SCTX2 IN zh_concat_im) RETURN NUMBER
14 );

创建type body

 1 CREATE OR REPLACE TYPE BODY zh_concat_im 
 2 IS
 3 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im)
 4 RETURN NUMBER
 5 IS
 6 BEGIN
 7 SCTX := zh_concat_im(NULL) ;
 8 RETURN ODCICONST.SUCCESS;
 9 END;
10 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,
11 P1 IN VARCHAR2)
12 RETURN NUMBER
13 IS
14 BEGIN
15 IF(CURR_STR IS NOT NULL) THEN
16 CURR_STR := CURR_STR || ',' || P1;
17 ELSE
18 CURR_STR := P1;
19 END IF;
20 RETURN ODCICONST.SUCCESS;
21 END;
22 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,
23 RETURNVALUE OUT VARCHAR2,
24 FLAGS IN NUMBER)
25 RETURN NUMBER
26 IS
27 BEGIN
28 RETURNVALUE := CURR_STR ;
29 RETURN ODCICONST.SUCCESS;
30 END;
31 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,
32 SCTX2 IN zh_concat_im)
33 RETURN NUMBER
34 IS
35 BEGIN
36 IF(SCTX2.CURR_STR IS NOT NULL) THEN
37 SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR ;
38 END IF;
39 RETURN ODCICONST.SUCCESS;
40 END;
41 END;

创建函数

1 CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2)
2 RETURN VARCHAR2 AGGREGATE USING zh_concat_im ;

创建同义词

create public synonym wm_concat for wmsys.wm_concat