Oracle12c pdb下wm_concat问题处理

针对oracle 12c 版本且多pdb wm_concat问题

首先使用dba账号登录oracle数据库 cdb

--解锁wmsys用户
alter user wmsys account unlock; 
--并为wmsys用户授权，可根据需要授权，不建议授权所有权限
grant all privileges to wmsys; 
--如果不知道wmsys用户的密码，可以修改其密码
alter user wmsys identified by wmsys; 

DBA权限用户登入PDB，并授权

grant dba to wmsys;

使用wmsys用户登录数据库

--在wmsys下创建可用的wm_concat函数，直接执行以下语句--定义类型
CREATE OR REPLACE TYPE WM_CONCAT_IMPL 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;
/