目录

oracle中wm_concat

oracle中wm_concat标识符无效原因：
11gr2和12c上已经摒弃了wm_concat函数，当时我们很多程序员在程序中确使用了该函数，导致程序出现错误，为了减轻程序员修改程序的工作量，只有通过手工创建个wm_concat函数，来临时解决该问题，但是注意，及时创建了该函数，在使用的过程中，也需要用to_char(wm_concat())方式，才能完全替代之前的应用。

解决方案

1. 创建包、包体和函数

以sys用户登录数据库，执行下面的命令

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 ;

2. 创建同义词并授权

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;