升级到oracle 19.8后vm_concat函数不可用怎么解决

作者：Digital Observer（施嘉伟）
Oracle ACE Pro: Database
PostgreSQL ACE Partner
11年数据库行业经验，现主要从事数据库服务工作
拥有Oracle OCM、DB2 10.1 Fundamentals、MySQL 8.0 OCP、WebLogic 12c OCA、KCP、PCTP、PCSD、PGCM、OCI、PolarDB技术专家、达梦师资认证、数据安全咨询高级等认证
ITPUB认证专家、PolarDB开源社区技术顾问、HaloDB技术顾问、TiDB社区技术布道师、青学会MOP技术社区专家顾问、国内某高校企业实践指导教师
公众号/墨天轮：Digital Observer；CSDN/PGfans：施嘉伟；ITPUB：sjw1933

最近，有客户反馈，vm_concat函数在19.8中怎么不存在了，在19.7中还存在呢
借此机会对vm_concat函数做一个全面的了解,
首先我们先介绍一下vm_concat函数

1.vm_concat函数的介绍

vm_concat函数属于VMSYS用户，其可以实现将多列记录聚合为一列记录，实现数据的压缩。

我们在Oracle11.2.0.4用实验来展示下该函数的效果

首先我们创建一个测试表，并插入三行数据。

create table test (id number , name varchar2(20));
insert into test values(1,'a');
insert into test values(1,'b');
insert into test values(2,'c');
commit;
复制

使用PLSQL查询看下数据信息:

select * from test;
复制

使用wm_concat将多列记录聚合为一列，按id分组。

select id,vmsys.wm_concat(name) as name from test group by id;
复制

在 PLSQL中执行.

可以看出显示数据类型为CLOB类型

所以如果返回的数据类型为CLOB,我们需要使用to_char进行类型转换

select id,to_char(vmsys.wm_concat(name)) as name from test group by id;
复制

注意并不是所有版本的vm_concat函数返回的数据类型都为CLOB

2.不同数据库版本中wm_concat的差异：

vm_concat属于Oracle内部函数，Oracle官方不会发布内部函数的变更信息，所以需要我们自己在各版本中进行测试。

10g以及11g： 在10g以及11g中该函数可以正常使用

但是在10g和11g中需要注意的是，vm_concat返回的数据类型是不同的：

在10.2.0.4/11.1.0.7/11.2.0.1 vm_concat返回的是数据类型为varchar2类型:

SQL> desc wmsys.wm_concat;
FUNCTION wmsys.wm_concat RETURNS VARCHAR2 
Argument Name      Type           In/Out  Default?

----------------------- ------------------------ -------- ---------

P1           VARCHAR2         IN
复制

在10.2.0.5/11.2.0.2中，vm_concat返回的数据类型为clob类型:

SQL> desc wmsys.wm_concat;

FUNCTION wmsys.wm_concat RETURNS CLOB

Argument Name      Type           In/Out  Default?

----------------------- ------------------------ -------- ---------

P1           VARCHAR2         IN

复制

12c：

经过测试,在12.1/12.2中，vm_concat函数Oracle已经不再支持使用，Oracle推荐使用分析函数LISTAGG代替vm_concat函数的使用。

19c：

经过测试,在19.3/19.7/19.8中，vm_concat函数Oracle依旧不再支持使用。

3.wm_concat的继任者LISTAGG

分析函数LISTAGG介绍：

作为单一集合，LISTAGG它对所有行进行操作并返回单个输出行。

作为分组集合，此函数将对GROUP BY子句定义的每个组进行操作并返回输出行。

分析函数LISTAGG使用方法：

listagg(measur_expr，delimiter) within group ( order by order_by_clause); 

复制

解释：

measure_expr 可以是基于任何列的表达式

delimiter 分隔符，默认为NULL

order_by_clause 决定了列值的拼接顺序

例子：

select listagg(name,',')within group(order by id) from test;
复制

select id,listagg(name,’,’)within group(order by id) from test group by id;

4.oracle关于wm_concat的建议

由于vm_concat属于一个内部函数，并且Oracle官方不会发布内部函数的变更信息,所以Oracle建议不要在应用程序中使用该函数，前面我们已经说了vm_concat函数的前世今生，我们可以想象一下，如果我们的系统使用了大量的vm_concat函数，当某一天数据库升级时，会出现很大的问题。所以Oracle建议使用分析函数LISTAGG来代替vm_concat函数的使用，或者自己编写一个函数实现vm_concat的功能，分析函数LISTAGG的用法前面已经分析过了，那么如何自己编写一个函数来实现vm_concat呢

5.手动编写函数实现vm_concat

(1)自定义函数类型

CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT
(
STR VARCHAR2(32767), 
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(cs_ctx IN OUT WM_CONCAT_IMPL) RETURN NUMBER,    
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,PT IN VARCHAR2) RETURN NUMBER,MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,RETURNVALUE OUT VARCHAR2,FLAGS IN NUMBER)RETURN NUMBER,MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,cs_ctx2 IN WM_CONCAT_IMPL) RETURN NUMBER
);
复制

(2)创建函数主体

CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(cs_ctx IN OUT WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
cs_ctx := WM_CONCAT_IMPL(NULL) ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,PT IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
IF(STR IS NOT NULL) THEN
STR := STR || ',' || PT;
ELSE
STR := PT;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,RETURNVALUE OUT VARCHAR2,FLAGS IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURNVALUE := STR ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,cs_ctx2 IN WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
IF(cs_ctx2.STR IS NOT NULL) THEN
SELF.STR := SELF.STR || ',' || cs_ctx2.STR ;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;
复制

(3)创建函数

CREATE OR REPLACE FUNCTION wm_concat(PT VARCHAR2)

RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL; 
复制

(4)创建同义词

create public synonym WM_CONCAT_IMPL for sys.WM_CONCAT_IMPL;
create public synonym wm_concat for sys.wm_concat;
grant execute on WM_CONCAT_IMPL to public;
grant execute on wm_concat to public;
复制

vm_concat函数自建完毕，感兴趣的同学可以试一试。