-
wmsys.wmconcat mysql_替代Oracle函数wmsys_wm_concat()函数
2021-01-25 20:03:31RETURN ODCICONST.SUCCESS;.../函数:create or replace FUNCTION zh_concat(P1 VARCHAR2) RETURN VARCHAR2 AGGREGATE U1.select postgresql('emp','ename') from dual2.sys_connect_by_path3.create or replace ...RETURN ODCICONST.SUCCESS; END;END;/函数:create or replace FUNCTION zh_concat(P1 VARCHAR2) RETURN VARCHAR2 AGGREGATE U
1.
select postgresql('emp','ename') from dual
2.sys_connect_by_path
3.create or replace TYPE zh_concat_im
AUTHID CURRENT_USER AS OBJECT
(
CURR_STR VARCHAR2(32767),
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 VARCHAR2,
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 VARCHAR2,
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 VARCHAR2 AGGREGATE USING zh_concat_im ;
修改标红部门,,可调整字段间分隔符
本文原创发布php中文网,转载请注明出处,感谢您的尊重!
-
oracle数据库wm_concat函数替代方案
2019-09-12 09:44:34oracle数据库wm_concat有长度限制,如果长度超长会显示如下提示: 例如原始SQL: select rcid, wm_concat(column_comment) as des from dm_s_resource_catalog_metadata where rcid=‘d6b3d14a17314cdbbe750a57428c4...oracle数据库wm_concat有长度限制,如果长度超长会显示如下提示:
例如原始SQL:
select rcid, wm_concat(column_comment) as des from dm_s_resource_catalog_metadata where rcid=‘d6b3d14a17314cdbbe750a57428c4abc’ group by rcid替代方案:
select XMLAGG(XMLELEMENT(E, column_comment || ‘,’)).EXTRACT(’//text()’).getclobval() des from dm_s_resource_catalog_metadata where rcid=‘d6b3d14a17314cdbbe750a57428c4abc’ -
oralce函数wm_concat 替代办法
2017-11-16 16:51:521.该函数不是oracle公开的系统函数,它的用户是wmsys,而不是sys或者system,oracle很有可能在版本升级或者补丁的时候取消或者修改这个函数甚至用户,这种变化oracle是不会公开的。所有可能会由于这个变化而导致异常...原帖地址:http://blog.csdn.net/pengdingxu10/article/details/46438567
oracle11g后 推荐使用listagg函数。
vm_concat
1.该函数不是oracle公开的系统函数,它的用户是wmsys,而不是sys或者system,oracle很有可能在版本升级或者补丁的时候取消或者修改这个函数甚至用户,这种变化oracle是不会公开的。所有可能会由于这个变化而导致异常。
2.大量使用这个函数也会导致临时表空间爆满,这是因为在10.2.0.5中,使用wmsys.wm_concat返回的结果格式是CLOB,CLOB占用的临时表空间只有在连接释放后才会释放,部分通过连接池连接数据库的长连接很有可能导致CLOB占用临时表空间不断累积增大,会导致临时表空间爆满的故障
3.如果是在程序中大量使用这个函数的话会引起enq:TT的锁,可能会导致某些对象被锁。
解决办法:
官方文档给了建议,可以参考该函数自己建立一个函数实现相同的行列转换功能。
CREATE OR REPLACE TYPE en_concat_im AUTHID CURRENT_USER AS OBJECT
(
CURR_STR VARCHAR2(32767),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT en_concat_im)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT en_concat_im,
P1 IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN en_concat_im,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT en_concat_im,
SCTX2 IN en_concat_im) RETURN NUMBER
);CREATE OR REPLACE TYPE BODY en_concat_im IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT en_concat_im)
RETURN NUMBER IS
BEGIN
SCTX := en_concat_im(NULL);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT en_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 en_concat_im,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURNVALUE := CURR_STR;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT en_concat_im,
SCTX2 IN en_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 en_concat(P1 VARCHAR2) RETURN VARCHAR2
AGGREGATE USING 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这个类型,会不会也会临时表空间增大,目前没有测试过。
-
替代oralce的wm_concat函数
2018-07-30 15:28:23wm_concat函数是oracle拼接字符串常用函数,但是这函数并不是oracle官方推荐的,估计oracle在后续更新中会悄无声息的去除,在oracle 11g之前可以使用wm_concat,但是如果拼接字符串超过3000就会报错 Ora...个人技术网站 欢迎关注
原文链接:https://blog.csdn.net/m0_37548754/article/details/78553037
wm_concat函数是oracle拼接字符串常用函数,但是这函数并不是oracle官方推荐的,估计oracle在后续更新中会悄无声息的去除,在oracle 11g之前可以使用wm_concat,但是如果拼接字符串超过3000就会报错
Oracle ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小这个错误
解决办法:
官方建议自己建立一个函数实现相同的行列转换功能
第一步执行:
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 ;
实际使用例子:仅供参考
直接使用zh_concat替换wm_concat函数即可 不用动原有sql结构,
在此作为事件记录
-
wm_concat 函数在PG中替代
2018-08-07 10:02:42WM_CONCAT 经常使用到行转列上,早期的代码里这个函数用的会比较多,但是可惜在12c中,这个函数已经过期了: WM_CONCAT is gone in 12c. Use LISTAGG instead ... -
在Oracle12C数据库增加wm_concat函数
2020-06-02 09:51:07导致程序出现错误,为了减轻程序员修改程序的工作量,只有通过手工创建个wm_concat函数,来临时解决该问题,但是注意,即使创建了该函数,在使用的过程中,也需要用to_char(wm_concat())方式,才能完全替代之前的... -
oracle中替代wm_concat函数的方法
2017-05-26 10:06:00racle数据库中,不许用wm_concat函数,合并列,希望得到与wm_concat 一样的结果: s_type s2 水果 葡萄,哈密瓜,香瓜,火龙果 蔬菜 西兰花,茼蒿,茄子 坚果 核桃,巴旦木 数据如下: create table t_thz_1( n_id number... -
在12C上创建wm_concat函数
2020-05-26 08:56:54导致程序出现错误,为了减轻程序员修改程序的工作量,只有通过手工创建个wm_concat函数,来临时解决该问题,但是注意,及时创建了该函数,在使用的过程中,也需要用to_char(wm_concat())方式,才能完全替代之前的... -
oracle 创建 wm_concat 列转行 函数
2018-08-14 17:04:37导致程序出现错误,为了减轻程序员修改程序的工作量,只有通过手工创建个wm_concat函数,来临时解决该问题,但是注意,及时创建了该函数,在使用的过程中,也需要用to_char(wm_concat())方式,才能完全替代之前的... -
在oracle12C上创建wm_concat函数
2017-05-02 11:27:26导致程序出现错误,为了减轻程序员修改程序的工作量,只有通过手工创建个wm_concat函数,来临时解决该问题,但是注意,及时创建了该函数,在使用的过程中,也需要用to_char(wm_concat())方式,才能完全替代之前的... -
在Oracle 上补充创建wm_concat函数
2017-02-23 08:55:09导致程序出现错误,为了减轻程序员修改程序的工作量,只有通过手工创建个wm_concat函数,来临时解决该问题,但是注意,及时创建了该函数,在使用的过程中,也需要用to_char(wm_concat())方式,才能完全替代之前的... -
oracle12c取消wm_concat函数,如何重建或使用替代方案?
2019-05-08 09:51:24wm_concat函数是oracle的非公开函数,在新版的oracle中不支持该函数。虽然其不支持,但是为了方便统计数据,我们是可以重新创建该函数的,或者,我们也可以使用其替代函数,listagg,下面我们就这两种方式分别写出... -
ORA-22922 错误 wm_concat 函数的替代方案
2015-08-07 23:27:23create or replace TYPE zh_concat_im AUTHID CURRENT_USER AS OBJECT ( CURR_STR VARCHAR2(32767), STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im) RETURN NUMBER, MEMBER FUNCTIO -
00904: "WM_CONCAT": invalid identifier -oracle12.0后无法使用WMSYS_WMCONCAT()函数 使用listagg替代实现...
2018-10-09 10:55:36由于WMSYS_CONCAT()只适用于oracle10.0及之前的版本,且该oracle函数并不是官方规定的函数,导致在使用的过程中会...WM_CONCAT": invalid identifier 如下述sql语句: select WM_CONCAT(STAGE) AS stag... -
oracle数据库wm_concat函数在PostgreSQL(pg)数据库的替代方案
2018-04-12 19:47:09oracle 数据库中比较常用到wmsys.wm_concat () 来连接字符串把多行的数据库连接到一起(默认是用逗号分割的) oracle 转pg 需要一个替代方案,尽量的应用代码改动小,pg 还是很能干的,果然找到了一个替代方案[code... -
自定义函数替代WMSYS.WM_CONCAT
2018-08-08 10:46:58CREATE 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 ODCIAGGREGATE... -
"WM_CONCAT": 标识符无效
2018-09-05 11:24:14导致程序出现错误,为了减轻程序员修改程序的工作量,只有通过手工创建个wm_concat函数,来临时解决该问题,但是注意,及时创建了该函数,在使用的过程中,也需要用to_char(wm_concat())方式,才能完全替代之前的... -
oracle12 listagg 与 wm_concat行列转换
2018-01-02 20:22:00导致程序出现错误,为了减轻程序员修改程序的工作量,只有通过手工创建个wm_concat函数,来临时解决该问题,但是注意,即使创建了该函数,在使用的过程中,也需要用to_char(wm_concat())方式,才能完全替代之前的... -
oracle使用wm_concat() 获取行之后,打开报错,CLOB类型,以及替代方案
2020-06-09 15:54:46获取两个表的公共字段,使用了wm_concat() 函数,但是打开行的时候报错,因为是clob的类型。 解决方法: 1、 使用to_char函数,转成字符类型: select to_char(wm_concat(COLUMN_NAME)) from all_tab_columns a ... -
oracle12cwm_concat函数人工写行转列函数
2019-05-06 15:29:45导致程序出现错误,为了减轻程序员修改程序的工作量,只有通过手工创建个wm_concat函数,来临时解决该问题,但是注意,及时创建了该函数,在使用的过程中,也需要用to_char(wm_concat())方式,才能完全替代之前的...
-
阿里巴巴Java研发面试99题(含答案):JVM+Spring+MySQL+线程池+锁
-
常用WIFI密码字典(解压后1.2G)
-
常用的数字函数
-
Docker从入门到精通
-
2021-03-01
-
MaxScale 实现 MySQL 读写分离与负载均衡
-
技术书籍list
-
MySQL NDB Cluster 负载均衡和高可用集群
-
C/C++反汇编解密
-
基于SSM实现的房屋租赁系统【附源码】(毕设)
-
NFS 网络文件系统
-
GitTest-源码
-
零基础一小时极简以太坊智能合约开发环境搭建并开发部署
-
基于频移键控和强度调制标记的新型高速全光正交调制方案的研究
-
GPU的介绍以及原理的分析
-
统计每个月兔子的总数
-
【Python-随到随学】FLask第二周
-
深入浅出Kotlin协程
-
华为1+X——网络系统建设与运维(高级)
-
MySQL 高可用工具 heartbeat 实战部署详解