【函数】wm_concat包的订制

 函数wm_concat包的订制

 

 BLOG文档结构图

wpsE894.tmp 

 

 前言部分

 

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

① 本文在itpubhttp://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的最大归档日志号为33thread 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/,但是该函数不稳定,在10G11GR2上返回值不同,一个是字符串一个是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%';

应如下所示:

wpsE895.tmp 

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

 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;

 

 自己创建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

 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;

wpsE8A5.tmp 

但是如果聚合的内容太多就会报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很大,随便用,那作为开发人员,估计才不会考虑这么多的,不管白猫黑猫,抓住老鼠就是好猫。

 

  About Me

...............................................................................................................................

● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

● 本文在itpubhttp://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/viewspace-2124931/

● 本文博客园地址:http://www.cnblogs.com/lhrbest/p/5869463.html

● 本文pdf版:http://yunpan.cn/cdEQedhCs2kFz (提取码:ed9b

● 小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群:230161599     微信群:私聊

● 联系我请加QQ好友(642808185),注明添加缘由

● 于 2016-09-13 09:00~ 2016-09-13 11:3在中行完成

● 文章内容来源于小麦苗的学习笔记部分整理自网络,若有侵权或不当之处还请谅解!

● 【版权所有,文章允许转载,但须以链接方式注明源地址,否则追究法律责任】

...............................................................................................................................

手机长按下图识别二维码或微信客户端扫描下边的二维码来关注小麦苗的微信公众号:xiaomaimiaolhr,免费学习最实用的数据库技术。

wpsE8A6.tmp

 

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。
数据迁移中有一种解决方案很有亮点,如果表的数据量大,迁移涉及的表不多,同时对于维护时间有要求的情况下,物化视图的prebuilt方式就是一种很不错的选择。 大体的步骤和方法如下: 假设源环境是test_source,目标环境是test_target 在源环境中test_source的操作如下: Create table test_mv as select *from all_objects  ; alter table test_mv modify(object_id primary key); creat

【MySQL】5.7新特性之六 - 2016-09-15 17:09:05

写在前面   本系列文章基于 5.7.12 版本讲述MySQL的新特性。从安装,文件结构,SQL ,优化 ,运维层面 复制,GITD等几个方面展开介绍 5.7 的新特性和功能。同时也建议大家跟踪官方blog和官方文档,以尽快知悉其新的变化。 6.1  优化(工具方面)增强   5.7 版本中如果一个会话正在执行sql,且该sql 是支持explain的,那么我们可以通过指定会话id,查看该sql的执行计划。 EXPLAIN  [ options ]   FOR  CONNECTION connection
工具用于查看指定时间内show global status指标的变化,能够帮助运维人员了解系统负载的走势 本工具在5.5 5.6 5.7中测试通过 源码我放在百度云盘了 http://pan.baidu.com/s/1mhIeKp6 源码一共5个文件 conmysql.c main.c findv.c other.c type.h 如果源码编译使用如下方法 1、建立一个MYSQL用户用于监控,不需要什么权限只要能够show global status 即可 mysql create user mmon@'l
本文以双节点RAC为例,揭示了在执行select及DML操作过程中,Cache Fusion在幕后是如何对block进行锁管理的。 ###实例1上查询scott.t0820_1表 select * from scott.t0820_1;         ID ----------          2 select dbms_rowid.rowid_relative_fno(rowid) rfno,dbms_rowid.rowid_block_number(rowid) blkno from scott.
【故障处理】队列等待之 enq: US - contention 案例 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ① enq: US - contention 等待事件的解决 ② 一般等待事件的解决办法 ③ 队列等待的基本知识   Tips: ① 本文在 ITpub ( http://blog.itpub.net/26736162 )、博客园
    在Oracle 10g的中搭建Data Guard环境真是一个纠结,目前大体都是采用两种方式,一种是rman备份,一种是duplicate的方式,但是这两个地方不够让我满意,一来是rman备份数据量不小,需要先在本地生成备份,然后拷贝到备库去,这个搭建周期略长,另外一个就是推荐的方式duplicate,在10g中有些鸡肋的味道,本地备份,然后拷贝到备库,然后动用duplicate的方式,这样的方式还不如手工rman的方式同步来得顺心顺意,所以在10g中我是不怎么喜欢duplicate方式。当然11g
【故障处理】队列等待之 TX - allocate ITL entry 引起的死锁处理 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ① enq: TX - allocate ITL entry 等待事件的解决 ② 一般等待事件的解决办法 ③ 队列等待的基本知识 ④  ITL 死锁解决 ⑤  ITL 死锁模拟 ⑥  Merge 语句的非关联形式的查
【技巧】如何使用客户端发布 BLOG+ 如何快速发布微信公众号文章   1    BLOG 文档结构图     2    前言部分   2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ①  如何使用客户端发布 BLOG (重点) ②  如何快速发布微信公众号文章 ③  如何批量上传图片到blog ④  如何批量上传图片到微信公众号上     Tips: ① 本文在 itpub ( http://blog.itpu

降低高水位线的方法 - 2016-09-13 14:09:09

准备工作,创建一张表: CREATE TABLE TEST2 (     ID    NUMBER(10),     NAME  VARCHAR2(32) ); 1、移动表: SQL begin   2  for i in 1..10000 loop   3  insert into test2 values(i,'bbb');   4  end loop;   5  commit;   6  end;   7  / PL/SQL procedure successfully completed. SQL
预备知识: 1)row cache lock事件   --内存share pool分为library cache,dictionary cache;而row cache lock对象分布在dictionary cache中,是由于对于字典缓冲的访问造成的;   --这类属于latch类的资源竞争,相当耗CPU,如果并发量大的话,容易down机 如果发现这个等待十分高,一般来说可能由于2种原因,一是共享池太小了,需要增加共享池,另外一种情况是SQL分析过于频繁,对于共享池的并发访问量过大。对于任何一种情况,绝