转自:http://blog.csdn.net/wenzhongyan/article/details/43149757
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
- /