【函数】wm_concat包的订制

1  BLOG文档结构图

 

 

 

2  前言部分

 

 

2.1  导读和注意事项

各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：

① 利用系统包创建WM_CONCAT函数（重点）

② ORA-00904: "wm_concat":invalid identifier错误解决

③ 订制自己的WM_CONCAT函数

④ listagg分析函数的使用

⑤ ORA-01489: result of string concatenation is too long的错误解决

 

2.2  相关参考文章链接

行转列参考文章：http://blog.itpub.net/26736162/viewspace-1272538/

 

 

2.3  本文简介

WMSYS用户下的WM_CONCAT函数有很重要的用途，比如行转列（http://blog.itpub.net/26736162/viewspace-1272538/），但是该函数不稳定，在10G和11GR2上返回值不同，一个是字符串一个是CLOB，而且12C上已经摒弃了WM_CONCAT函数，但是我们很多程序员在程序中使用了该函数，若是系统升级就会导致程序出现错误，为了减轻程序员修改程序的工作量，只有创建这个WM_CONCAT函数来解决该问题。

一般情况下报错信息，ORA-00904: "wm_concat":invalid identifier，查询DBA_OBJECTS视图，也未发现wm_concat的相关信息。正常情况下查询，

SQL> SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE 'WM_CONCAT%';

应如下所示：

 

解决办法有2种，一种是采用Oracle本身的脚本来创建WM_CONCAT函数，一种是采用自己创建的函数来完成这个功能。

 

3  用Oracle自带脚本重建WMSYS用户的WMSYS.WM_CONCAT函数

运行如下脚本卸载WMSYS：

@$ORACLE_HOME/rdbms/admin/owmuinst.plb

运行如下脚本执行安装WMSYS：

@$ORACLE_HOME/rdbms/admin/owminst.plb

解锁wmsys用户：

ALTER USER WMSYS ACCOUNT UNLOCK;

 

 

4  自己创建wmsys

 

4.1  订制脚本

若只是某个用户使用，那么我们可以不用刻意去创建wmsys用户，可以在当前用户下运行脚本，生成WM_CONCAT函数，为了和系统的函数名区别开来，我们也可以修改函数名称，订制自己的脚本。

 

4.1.1  无分隔符，返回CLOB

创建函数的脚本如下：

CREATE OR REPLACE TYPE WM_CONCAT_IMPL_CLOB_NULL_LHR AUTHID CURRENT_USER AS OBJECT (   CURR_STR CLOB,   STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR)     RETURN NUMBER,   MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,                                        P1   IN CLOB) RETURN NUMBER,   MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_CLOB_NULL_LHR,                                          RETURNVALUE OUT CLOB,                                          FLAGS       IN NUMBER)     RETURN NUMBER,   MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,                                      SCTX2 IN WM_CONCAT_IMPL_CLOB_NULL_LHR)     RETURN NUMBER ); / CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_CLOB_NULL_LHR IS   STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR)     RETURN NUMBER IS   BEGIN     SCTX := WM_CONCAT_IMPL_CLOB_NULL_LHR(NULL);     RETURN ODCICONST.SUCCESS;   END;   MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,                                        P1   IN CLOB) 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 WM_CONCAT_IMPL_CLOB_NULL_LHR,                                          RETURNVALUE OUT CLOB,                                          FLAGS       IN NUMBER) RETURN NUMBER IS   BEGIN     RETURNVALUE := CURR_STR;     RETURN ODCICONST.SUCCESS;   END;   MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,                                      SCTX2 IN WM_CONCAT_IMPL_CLOB_NULL_LHR)     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; /   CREATE OR REPLACE FUNCTION WM_CONCAT_CLOB_NULL_LHR(P1 VARCHAR2) RETURN CLOB   AGGREGATE USING WM_CONCAT_IMPL_CLOB_NULL_LHR; /   CREATE PUBLIC SYNONYM WM_CONCAT_CLOB_NULL_LHR FOR WM_CONCAT_CLOB_NULL_LHR; GRANT EXECUTE ON WM_CONCAT_CLOB_NULL_LHR TO PUBLIC;

 

测试案例，注意函数的返回值是无分隔符的CLOB，在PL/SQL中注意使用to_char进行转换：

SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);      USER_ID ----------          0          5   SYS@lhrdb21> SELECT WM_CONCAT_CLOB_NULL_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);   WM_CONCAT_CLOB_LHR_NULL(D.USER_ID) -------------------------------------------------------------------------------- 05   SYS@lhrdb21>

 

 

4.1.2  逗号分隔符，返回CLOB

创建函数的脚本如下：

CREATE OR REPLACE TYPE WM_CONCAT_IMPL_CLOB_LHR AUTHID CURRENT_USER AS OBJECT (   CURR_STR CLOB,   STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_LHR)     RETURN NUMBER,   MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_LHR,                                        P1   IN CLOB) RETURN NUMBER,   MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_CLOB_LHR,                                          RETURNVALUE OUT CLOB,                                          FLAGS       IN NUMBER)     RETURN NUMBER,   MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_LHR,                                      SCTX2 IN WM_CONCAT_IMPL_CLOB_LHR)     RETURN NUMBER ); / CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_CLOB_LHR IS   STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_LHR)     RETURN NUMBER IS   BEGIN     SCTX := WM_CONCAT_IMPL_CLOB_LHR(NULL);     RETURN ODCICONST.SUCCESS;   END;   MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_LHR,                                        P1   IN CLOB) 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 WM_CONCAT_IMPL_CLOB_LHR,                                          RETURNVALUE OUT CLOB,                                          FLAGS       IN NUMBER) RETURN NUMBER IS   BEGIN     RETURNVALUE := CURR_STR;     RETURN ODCICONST.SUCCESS;   END;   MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_LHR,                                      SCTX2 IN WM_CONCAT_IMPL_CLOB_LHR)     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; /   CREATE OR REPLACE FUNCTION WM_CONCAT_CLOB_LHR(P1 VARCHAR2) RETURN CLOB   AGGREGATE USING WM_CONCAT_IMPL_CLOB_LHR; /   CREATE PUBLIC SYNONYM WM_CONCAT_CLOB_LHR FOR WM_CONCAT_CLOB_LHR; GRANT EXECUTE ON WM_CONCAT_CLOB_LHR TO PUBLIC;

 

 

测试案例，注意函数的返回值是以逗号为分隔符的CLOB，在PL/SQL中注意使用to_char进行转换：

SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);      USER_ID ----------          0          5 SYS@lhrdb21> SELECT WM_CONCAT_CLOB_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);   WM_CONCAT_LHR(D.USER_ID) -------------------------------------------------------------------------------- 0,5

 

 

4.1.3  逗号分隔符，返回字符串

创建函数的脚本如下：

CREATE OR REPLACE TYPE WM_CONCAT_IMPL_STRINGS_LHR AUTHID CURRENT_USER AS OBJECT (   CURR_STR VARCHAR2(32767),   STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_STRINGS_LHR)     RETURN NUMBER,   MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_STRINGS_LHR,                                        P1   IN VARCHAR2) RETURN NUMBER,   MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_STRINGS_LHR,                                          RETURNVALUE OUT VARCHAR2,                                          FLAGS       IN NUMBER)     RETURN NUMBER,   MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_STRINGS_LHR,                                      SCTX2 IN WM_CONCAT_IMPL_STRINGS_LHR)     RETURN NUMBER ); / CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_STRINGS_LHR IS   STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_STRINGS_LHR)     RETURN NUMBER IS   BEGIN     SCTX := WM_CONCAT_IMPL_STRINGS_LHR(NULL);     RETURN ODCICONST.SUCCESS;   END;   MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_STRINGS_LHR,                                        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 WM_CONCAT_IMPL_STRINGS_LHR,                                          RETURNVALUE OUT VARCHAR2,                                          FLAGS       IN NUMBER) RETURN NUMBER IS   BEGIN     RETURNVALUE := CURR_STR;     RETURN ODCICONST.SUCCESS;   END;   MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_STRINGS_LHR,                                      SCTX2 IN WM_CONCAT_IMPL_STRINGS_LHR)     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; /   CREATE OR REPLACE FUNCTION WM_CONCAT_STRINGS_LHR(P1 VARCHAR2) RETURN VARCHAR2   AGGREGATE USING WM_CONCAT_IMPL_STRINGS_LHR; /   CREATE PUBLIC SYNONYM WM_CONCAT_STRINGS_LHR FOR WM_CONCAT_STRINGS_LHR; GRANT EXECUTE ON WM_CONCAT_STRINGS_LHR TO PUBLIC;

 

测试案例，注意函数的返回值是以逗号为分隔符的字符串：

SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);      USER_ID ----------          0          5   SYS@lhrdb21> SELECT WM_CONCAT_STRINGS_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);   WM_CONCAT_STRINGS_LHR(D.USER_ID) --------------------------------------------------- 0,5

 

5  listagg的使用

这是一个Oracle的列转行函数：LISTAGG()

with temp as( 

  select 'China' nation ,'Guangzhou' city from dual union all 

  select 'China' nation ,'Shanghai' city from dual union all 

  select 'China' nation ,'Beijing' city from dual union all 

  select 'USA' nation ,'New York' city from dual union all 

  select 'USA' nation ,'Bostom' city from dual union all 

  select 'USA' nation ,'Bostom' city from dual union all 

  select 'Japan' nation ,'Tokyo' city from dual  

) 

select nation,listagg(city,',') within GROUP (order by city) 

from temp 

group by nation;

 

但是如果聚合的内容太多就会报ORA-01489: result of string concatenation is too long的错误，这个时候可以从业务的角度去修改SQL，也可以使用WM_CONCAT函数返回CLOB类型来解决这个问题。如下聚合DBA_OBJECTS中的OBJECT_NAME就会报错：

SELECT LISTAGG(OBJECT_NAME, ',') WITHIN GROUP(ORDER BY OBJECT_NAME)

  FROM DBA_OBJECTS D;

报错：ORA-01489: result of string concatenation is too long

解决：可以用WM_CONCAT返回CLOB类型即可。SELECT WM_CONCAT_CLOB_LHR(D.OBJECT_NAME) FROM DBA_OBJECTS D;

 

注意：有关WM_CONCAT函数返回CLOB类型的性能问题，我们本篇文章不讨论，聚合的内容多了，自然就慢，到底是避免出ORA-01489错误还是要结果，这个还得根据自己的情况权衡决定，比如有的系统tmp很大，随便用，那作为开发人员，估计才不会考虑这么多的，不管白猫黑猫，抓住老鼠就是好猫。