【问题描述】
如果源端Oracle是12C及以上版本，而且是从11G等低版本升级上来的，其中的一些数据库函数可能已经不支持了，如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 WM_CONCAT_IMPL;
create public synonym wm_concat for wm_concat;
grant execute on WM_CONCAT_IMPL to public;
【迁移数据】开始迁移数据。