【函数】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的错误解决
Tips：
① 本文在itpub（http://blog.itpub.net/26736162）、博客园(http://www.cnblogs.com/lhrbest)和微信公众号（xiaomaimiaolhr）有同步更新。
② 文章中用到的所有代码，相关软件，相关资料请前往小麦苗的云盘下载（http://blog.itpub.net/26736162/viewspace-1624453/）。
③ 若网页文章代码格式有错乱，推荐使用360浏览器，也可以下载pdf格式的文档来查看，pdf文档下载地址：http://blog.itpub.net/26736162/viewspace-1624453/，另外itpub格式显示有问题，也可以去博客园地址阅读。
④ 本篇BLOG中命令的输出部分需要特别关注的地方我都用灰色背景和粉红色字体来表示，比如下边的例子中，thread 1的最大归档日志号为33，thread 2的最大归档日志号为43是需要特别关注的地方；而命令一般使用黄色背景和红色字体标注；对代码或代码输出部分的注释一般采用蓝色字体表示。
|
List of Archived Logs in backup set 11 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48 1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58 2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49 2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53 [ZHLHRDB1:root]:/>lsvg -o T_XLHRD_APP1_vg rootvg [ZHLHRDB1:root]:/> 00:27:22 SQL> alter tablespace idxtbs read write; ====》2097152*512/1024/1024/1024=1G |
本文如有错误或不完善的地方请大家多多指正，ITPUB留言或QQ皆可，您的批评指正是我写作的最大动力。
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很大，随便用，那作为开发人员，估计才不会考虑这么多的，不管白猫黑猫，抓住老鼠就是好猫。