通过+wmsys.wm - concat+实现行列转换 - 图文 

警示:通过 wmsys.wm_concat 实现行列转换

最近在修改陕西现场反馈回来的bug时,出现了使用wmsys.wm_concat进行类型转换时发生ora-00600错误。

在网上流传很多文章，描述了使用 wmsys.wm_concat对象实现行列转换的方法，但是这种方法不被 Oracle所推荐。因为WMSYS用户用于 Workspace Manager，其函数对象可能因版本而不同。

这种变化在 11.2.0.3及10.2.0.5 中体现出来。原本 WM_CONCAT函数返回值为VARCHAR2变更为 CLOB。这一变化导致了很多程序的异常。

需要的话,可以用to_char()函数将clog类型进行转换

Oracle建议用户使用自定义函数来实现该功能，而不是使用 WorkSpace的这个内部函数。

请注意，在将数据库从其他版本升级到 10.2.0.5和11.2.0.3 中时，必须注意到，这个函数的返回值类型变化。

例如如下一个系列的函数，可以帮助用户构建自有的行列转换函数： SQL> create or replace TYPE en_concat_im 2 AUTHID CURRENT_USER AS OBJECT 3 (

4 CURR_STR VARCHAR2(32767),

5 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT en_concat_im) RETURN NUMBER,

6 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT en_concat_im, 7 P1 IN VARCHAR2) RETURN NUMBER,

8 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN en_concat_im, 9 RETURNVALUE OUT VARCHAR2, 10 FLAGS IN NUMBER) 11 RETURN NUMBER,

12 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT en_concat_im, 13 SCTX2 IN en_concat_im) RETURN NUMBER 14 ); 15 /

SQL> create or replace TYPE BODY en_concat_im 2 IS

3 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT en_concat_im) 4 RETURN NUMBER 5 IS 6 BEGIN

7 SCTX := en_concat_im(NULL) ; 8 RETURN ODCICONST.SUCCESS;

9 END;

10 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT en_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 en_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 en_concat_im, 32 SCTX2 IN en_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; 42 /

SQL> create or replace FUNCTION en_concat(P1 VARCHAR2) RETURN VARCHAR2 AGGREGATE USING en_concat_im ;

以上改变还存在一个潜在的性能风险，那就是使用 CLOB时会用到临时段，在高压力系统下，临时文件的大量占用会导致临时表空间的迅速增长 .

以上问题在版本升级时需要注意。