about me:oracle ace,optimistic,passionate and harmonious. focus on oracle programming,peformance tuning,db design, j2ee,linux/aix,web2.0 tech,etc
全部博文（145）
分类： oracle
2023-05-04 15:37:27
|
--注意使用具体的用户名代替如下的wmsys
--12c以上还要grant inherit privileges on user wmsys to public;grant inherit privileges on user sys to public; create or replace type wmsys.wm_concat_10g_impl authid current_user as object ( curr_str varchar2(32767), static function odciaggregateinitialize(sctx in out wm_concat_10g_impl) return number, member function odciaggregateiterate(self in out wm_concat_10g_impl, p1 in varchar2) return number, member function odciaggregateterminate(self in wm_concat_10g_impl, returnvalue out varchar2, flags in number) return number, member function odciaggregatemerge(self in out wm_concat_10g_impl, sctx2 in wm_concat_10g_impl) return number ); / create or replace type body wmsys.wm_concat_10g_impl is static function odciaggregateinitialize(sctx in out wm_concat_10g_impl) return number is begin sctx := wm_concat_10g_impl(null); return odciconst.success; end; member function odciaggregateiterate(self in out wm_concat_10g_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_10g_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_10g_impl, sctx2 in wm_concat_10g_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 wmsys.wm_concat(p1 varchar2) return varchar2 parallel_enable aggregate using wmsys.wm_concat_10g_impl; / -- 创建public同义词 create public synonym wm_concat for wmsys.wm_concat; -- 允许所有用户执行 grant execute on wmsys.wm_concat to public; -- 使用普通用户测试 select wm_concat(owner) from dba_objects where rownum<10; --12c还需要授权 grant inherit privileges on user wmsys to public; grant inherit privileges on user sys to public; |