oracle 11gr2和12c上已经摒弃了wm_concat函数，当时我们很多程序员在程序中确使用了该函数，导致程序出现错误，为了减轻程序员修
oracle 11gr2和12c上已经摒弃了wm_concat函数，当时我们很多程序员在程序中确使用了该函数，导致程序出现错误，为了减轻程序员修改程序的工作量，只有通过手工创建个wm_concat函数，来临时解决该问题，，但是注意，及时创建了该函数，在使用的过程中，也需要用to_char(wm_concat())方式，才能完全替代之前的应用。
一.解锁wmsys用户
alter user wmsys account unlock;
二.创建包、包体和函数
以wmsys用户登录数据库，执行下面的命令
create or replace type wm_concat_impl as object
-- authid current_user as object
(
curr_str varchar2(32767),
static function odciaggregateinitialize(sctx in out wm_concat_impl) return number,
member function odciaggregateiterate(self in out wm_concat_impl,
p1 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,
sctx2 in wm_concat_impl) return number
);
/
--定义类型body:
create or replace type body wm_concat_impl
is
static function odciaggregateinitialize(sctx in out wm_concat_impl)
return number
is
begin
sctx := wm_concat_impl(null) ;
return odciconst.success;
end;
member function odciaggregateiterate(self in out wm_concat_impl,
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,
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,
sctx2 in wm_concat_impl)
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(p1 varchar2)
return varchar2 aggregate using wm_concat_impl ;
/
三.创建同义词并授权
create public synonym wm_concat_impl for wmsys.wm_concat_impl
/
create public synonym wm_concat for wmsys.wm_concat
/
grant execute on wm_concat_impl to public
/
grant execute on wm_concat to public
/
本文永久更新链接地址：