目录
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;