关于oracle中的wmsys.wm_concat中的使用问题
来源:互联网 发布:手机相片编辑软件 编辑:程序博客网 时间:2024/06/05 08:15
这两天在项目中遇到了一个问题,就是系统中使用了wmsys.wm_concat引发的相关问题,PM也提议不要使用,原因有下:
1.该函数不是oracle公开的系统函数,它的用户是wmsys,而不是sys或者system,oracle很有可能在版本升级或者补丁的时候取消或者修改这个函数甚至用户,这种变化oracle是不会公开的。所有可能会由于这个变化而导致异常。
2.大量使用这个函数也会导致临时表空间爆满,这是因为在10.2.0.5中,使用wmsys.wm_concat返回的结果格式是CLOB,CLOB占用的临时表空间只有在连接释放后才会释放,部分通过连接池连接数据库的长连接很有可能导致CLOB占用临时表空间不断累积增大,会导致临时表空间爆满的故障
3.如果是在程序中大量使用这个函数的话会引起enq:TT的锁,可能会导致某些对象被锁。
解决办法:
官方文档给了建议,可以参考该函数自己建立一个函数实现相同的行列转换功能。
然后我上网找了一些资料,http://www.eygle.com/archives/2012/10/wmsys_wm_concat.html
确实可以实现跟wmsys.wm_concat一样的功能,下面是这篇文章的实现
create or replace TYPE en_concat_im
create or replace TYPE BODY en_concat_im
然后运行测试:select en_concat(username) from dba_users; ok可以实现行列转换。具体的性能分析可以浏览该网址的文章
但是我在运行的时候出了个问题,就是查询的表数据量比较大的时候会报:Oracle ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小这个错误
我当时不明白什么原因,后面百度了一下这个原因,看到了一篇文章:http://bbs.csdn.net/topics/360059765
楼主的问题:
使用wmsys.wm_concat多列合成一列遇到问题
ORA-22813: 操作数值超出系统的限制
往下看了之后对比了一下这个问题的解决办法,原理跟上面一样,也是自定义type来实现,但是有一个不同点的就是,上面的实现我标注的地方,返回的是varchar2类型的
在看看这篇文章里面的实现:
CREATE
OR
REPLACE
TYPE zh_concat_im
AUTHID
CURRENT_USER
AS
OBJECT
(
CURR_STR clob,
STATIC
FUNCTION
ODCIAGGREGATEINITIALIZE(SCTX
IN
OUT
zh_concat_im)
RETURN
NUMBER,
MEMBER
FUNCTION
ODCIAGGREGATEITERATE(SELF
IN
OUT
zh_concat_im,
P1
IN
VARCHAR2)
RETURN
NUMBER,
MEMBER
FUNCTION
ODCIAGGREGATETERMINATE(SELF
IN
zh_concat_im,
RETURNVALUE
OUT
clob,
FLAGS
IN
NUMBER)
RETURN
NUMBER,
MEMBER
FUNCTION
ODCIAGGREGATEMERGE(SELF
IN
OUT
zh_concat_im,
SCTX2
IN
zh_concat_im)
RETURN
NUMBER
);
CREATE
OR
REPLACE
TYPE BODY zh_concat_im
IS
STATIC
FUNCTION
ODCIAGGREGATEINITIALIZE(SCTX
IN
OUT
zh_concat_im)
RETURN
NUMBER
IS
BEGIN
SCTX := zh_concat_im(
NULL
) ;
RETURN
ODCICONST.SUCCESS;
END
;
MEMBER
FUNCTION
ODCIAGGREGATEITERATE(SELF
IN
OUT
zh_concat_im,
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
zh_concat_im,
RETURNVALUE
OUT
clob,
FLAGS
IN
NUMBER)
RETURN
NUMBER
IS
BEGIN
RETURNVALUE := CURR_STR ;
RETURN
ODCICONST.SUCCESS;
END
;
MEMBER
FUNCTION
ODCIAGGREGATEMERGE(SELF
IN
OUT
zh_concat_im,
SCTX2
IN
zh_concat_im)
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
zh_concat(P1 VARCHAR2)
RETURN
clob AGGREGATE USING zh_concat_im ;
这个定义的的clob类型,所有这个函数可以实现,没有出现了Oracle ORA-06502:这个错误,顿时恍然啊。
至于clob这个类型跟varchar2各位就查资料看看了,但是目前我还是有点不明白,这样实现也是用clob这个类型,会不会也会临时表空间增大,目前没有测试过。
以上就是这两天查资料看到的这些,有错的地方希望指出来了;我oracle也不是很熟哈!!!如果哪位大神能更好的说明的那就更好了。
- 关于oracle中的wmsys.wm_concat中的使用问题
- 关于oracle中的wmsys.wm_concat中的使用问题
- Oracle中的wmsys.wm_concat
- Oracle中的wmsys.wm_concat
- Oracle中的wmsys.wm_concat
- Oracle中的wmsys.wm_concat
- oracle中的wmsys.wm_concat()函数
- 关于ORACLE wmsys.wm_concat 行转列函数使用
- oracle内置函数 wmsys.wm_concat使用
- Oracle函数wmsys.wm_concat的使用
- Oracle函数wmsys.wm_concat的使用
- oracle wmsys.wm_concat(column)函数的使用
- oracle wmsys.wm_concat 函数的使用
- Oracle函数wmsys.wm_concat的使用
- 执行Oracle中的函数wm_concat报错oracle wmsys.wm_concat ORA-00932: 数据类型不一致: 应为 -, 但却获得 CLOB
- oracle wmsys.wm_concat函数
- oracle wmsys.wm_concat用法
- oracle之wmsys.wm_concat
- 计算题:装箱问题
- Mac上通过Linux命令行批量查找和替换文本
- QTableWidget 用法总结
- 人脉就是钱脉,培养人脉的100个技巧
- 微软100题(69)旋转数组中的最小元素(数组、算法)
- 关于oracle中的wmsys.wm_concat中的使用问题
- linux 命令行 I/O 重定向和管道
- 陈怡暖:闯关容易守关难,加息魔咒令黄金多头无法大施拳脚
- 14、oracle vm virtualbox & ubuntu-14.04网络及性能配置
- H.264 划分帧
- 一个看似不重要的环节——论库存重要性
- php编程学习笔记
- Oracle的一点注意点
- js_原型链