oracle10G升级到12C的问题

数据库字符集由16GBK，变成了32UTF8。数据库导出导入后，varchar2字段内容被截图的风险——在32UTF8下一个汉字占3个字符

首先我们比对了所有表内容是否一致，后续对所有表字段扩容

写了一个存储过程 

CREATE OR REPLACE PACKAGE BODY "PKG_HXDI_ORACLE_UPDATE_UTIL" IS

  /**********************************************************
  #PROCEDURE:：ORALCE 执行升级比对
  #author:diaoby
  #createdate:2018-12-2 13:00:24
  #example:
  ***********************************************************/
  PROCEDURE P_EXECORACLEUPGRADE IS
    V_USER       VARCHAR2(255); --用户名
    V_TABLE_NAME VARCHAR2(255); --表名
    V_COL_NAME   VARCHAR2(255); --字段名
    V_SQL        VARCHAR2(4000); --比对sql
    --返回表游标定义
    V_TABLE_CUR MYTYPE;
    V_TABLE_REC TABLE_REC;
    --返回字段游标定义
    V_COL_CUR MYTYPE;
    V_COL_REC COL_REC;
    --返回值游标定义
    V_VALUE_CUR MYTYPE;
    V_VALUE_REC V_REC;
  BEGIN
    V_USER      := 'GXMCPM_B_02';
    V_TABLE_CUR := F_GETALLTABLE(V_USER);
    --Fetch 循环  
    LOOP
      FETCH V_TABLE_CUR
        INTO V_TABLE_REC;
      EXIT WHEN V_TABLE_CUR%NOTFOUND;
      V_TABLE_NAME := V_TABLE_REC.TABLE_NAME;
      IF V_TABLE_NAME IS NOT NULL THEN
        V_COL_CUR := F_GETTABLECOL(V_TABLE_NAME);
        LOOP
          FETCH V_COL_CUR
            INTO V_COL_REC;
          EXIT WHEN V_COL_CUR%NOTFOUND;
          V_COL_NAME := V_COL_REC.CNAME;
          IF V_COL_NAME IS NOT NULL THEN
            V_SQL := F_GETSQL(V_TABLE_NAME, V_COL_NAME);
            IF V_SQL IS NOT NULL THEN
              --打开游标     
              OPEN V_VALUE_CUR FOR V_SQL;
              LOOP
                FETCH V_VALUE_CUR
                  INTO V_VALUE_REC;
                EXIT WHEN V_VALUE_CUR%NOTFOUND;
                IF V_VALUE_REC.PK_ID IS NOT NULL THEN
                  P_RECORDLOG(V_TABLE_NAME,
                              F_GETTABLEPRIMARY(V_TABLE_NAME),
                              V_VALUE_REC.PK_ID,
                              V_COL_NAME,
                              V_VALUE_REC.OLD_VALUE,
                              V_VALUE_REC.NEW_VALUE);
                END IF;
              END LOOP;
              CLOSE V_VALUE_CUR;
            END IF;
          END IF;
        END LOOP;
        CLOSE V_COL_CUR;
      END IF;
    END LOOP;
    CLOSE V_TABLE_CUR;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('异常：sql:' || V_SQL || ' SQLCODE:' || SQLCODE);
  END;

  /**********************************************************
  #PROCEDURE:：ORALCE 升级VARHCAR2 扩展2倍
  #author:diaoby
  #createdate:2018-12-2 13:00:24
  #example:
  ***********************************************************/
  PROCEDURE P_MODIFYVARCHAR IS
    V_USER       VARCHAR2(255); --用户名
    V_TABLE_NAME VARCHAR2(255); --表名
    V_COL_NAME   VARCHAR2(255); --字段名
    V_COL_WIDTH  NUMBER;
    V_SQL        VARCHAR2(4000); --比对sql
    --返回表游标定义
    V_TABLE_CUR MYTYPE;
    V_TABLE_REC TABLE_REC;
    --返回字段游标定义
    V_COL_CUR MYTYPE;
    V_COL_REC COL_REC;
  BEGIN
    V_USER      := 'GXMCPM_B_02';
    V_TABLE_CUR := F_GETALLTABLE(V_USER);
    --Fetch 循环  
    LOOP
      FETCH V_TABLE_CUR
        INTO V_TABLE_REC;
      EXIT WHEN V_TABLE_CUR%NOTFOUND;
      V_TABLE_NAME := V_TABLE_REC.TABLE_NAME;
      V_COL_CUR    := F_GETTABLECOL(V_TABLE_NAME);
      LOOP
        FETCH V_COL_CUR
          INTO V_COL_REC;
        EXIT WHEN V_COL_CUR%NOTFOUND;
        V_COL_NAME  := V_COL_REC.CNAME;
        V_COL_WIDTH := V_COL_REC.WIDTH;
        V_SQL       := 'alter table ' || V_TABLE_NAME || ' modify( ' ||
                       V_COL_NAME || ' VARCHAR2(' || 2 * V_COL_WIDTH || '))';
        --DBMS_OUTPUT.PUT_LINE(V_SQL);
        EXECUTE IMMEDIATE V_SQL;
        -- USING V_TABLE_NAME, V_COL_NAME, 2 * V_COL_WIDTH;
      END LOOP;
      CLOSE V_COL_CUR;
    END LOOP;
    CLOSE V_TABLE_CUR;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('异常：sql:' || V_SQL || 'SQLCODE:' || SQLCODE);
  END;

  /**********************************************************
  #function:：根据用户账号获取该用户下所有表结构排查记录表，返回游标
  #author:diaoby
  #createdate:2018-12-2 13:00:24
  #input:in_user  用户名
  #example:
  ***********************************************************/
  FUNCTION F_GETALLTABLE(IN_USER IN VARCHAR2) RETURN MYTYPE IS
    T_CUR MYTYPE;
  BEGIN
    OPEN T_CUR FOR
      SELECT DISTINCT T.TABLE_NAME
        FROM ALL_TABLES T
       WHERE T.OWNER = IN_USER
         /*AND T.TABLE_NAME = 'PD_TASK_PLAN_VER'*/
         AND T.TABLE_NAME != 'ORACLE_LOG';
    RETURN T_CUR;
  END;

  /**********************************************************
  #function:：根据表名获取所有varhcar2的表字段
  #author:diaoby
  #createdate:2018-12-2 13:00:24
  #input:in_table  表名
  #example:
  ***********************************************************/
  FUNCTION F_GETTABLECOL(IN_TABLE IN VARCHAR2) RETURN MYTYPE IS
    T_CUR MYTYPE;
  BEGIN
    OPEN T_CUR FOR
      SELECT A.CNAME, A.WIDTH
        FROM COL A
       WHERE A.TNAME = UPPER(IN_TABLE)
         AND COLTYPE = 'VARCHAR2'
       ORDER BY A.COLNO;
    RETURN T_CUR;
  END;

  /**********************************************************
  #function:：根据表名获取对应主键，可能是联合主键  返回，隔开的varhcar2字段
  #author:diaoby
  #createdate:2018-12-2 13:00:24
  #input:in_table  表名
  #example:
  ***********************************************************/
  FUNCTION F_GETTABLEPRIMARY(IN_TABLE IN VARCHAR2) RETURN VARCHAR2 IS
    RESULT_VALUE VARCHAR2(4000);
  BEGIN
    RESULT_VALUE := '';
    FOR REC IN (SELECT COL.COLUMN_NAME
                  FROM USER_CONSTRAINTS CON, USER_CONS_COLUMNS COL
                 WHERE CON.CONSTRAINT_NAME = COL.CONSTRAINT_NAME
                   AND CON.CONSTRAINT_TYPE = 'P'
                   AND COL.TABLE_NAME = UPPER(IN_TABLE)) LOOP
      IF RESULT_VALUE IS NULL THEN
        RESULT_VALUE := REC.COLUMN_NAME;
      ELSIF RESULT_VALUE IS NOT NULL THEN
        RESULT_VALUE := RESULT_VALUE || ',' || REC.COLUMN_NAME;
      END IF;
    END LOOP;
    RETURN RESULT_VALUE;
  END;

  /**********************************************************
  #function:：根据表名,字段名获取执行的sql
  #author:diaoby
  #createdate:2018-12-2 13:00:24
  #input:in_table  表名
  #input:in_col  字段名
  #example:
  ***********************************************************/
  FUNCTION F_GETSQL(IN_TABLE IN VARCHAR2, IN_COL IN VARCHAR2) RETURN VARCHAR2 IS
    RESULT_SQL   VARCHAR2(4000);
    PRIMARYS     VARCHAR2(4000);
    PRIMARYS_NUM NUMBER;
    V_COLUMN     VARCHAR2(200);
  BEGIN
    V_COLUMN   := '';
    RESULT_SQL := 'SELECT ';
    --根据表名获取主键
    PRIMARYS := F_GETTABLEPRIMARY(IN_TABLE);
    IF PRIMARYS IS NOT NULL THEN
      --获取主键数
      PRIMARYS_NUM := PKG_HXDI_UTIL.F_GETNUM(PRIMARYS, ',');
      FOR I IN 1 .. PRIMARYS_NUM LOOP
        IF I < PRIMARYS_NUM THEN
          RESULT_SQL := RESULT_SQL || ' A.' ||
                        PKG_HXDI_UTIL.F_GETSTR(PRIMARYS, ',', I) || '||';
        ELSIF I = PRIMARYS_NUM THEN
          RESULT_SQL := RESULT_SQL || ' A.' ||
                        PKG_HXDI_UTIL.F_GETSTR(PRIMARYS, ',', I) ||
                        ' PK_ID ,';
        END IF;
      END LOOP;
      RESULT_SQL := RESULT_SQL || ' A.' || IN_COL || ' OLD_VALUE ,' ||
                    ' B.' || IN_COL || ' NEW_VALUE';
      RESULT_SQL := RESULT_SQL || ' FROM ' || IN_TABLE || ' A ,' ||
                    IN_TABLE || '@GXPMSLINK B';
      RESULT_SQL := RESULT_SQL || ' WHERE';
      RESULT_SQL := RESULT_SQL || ' A.' || IN_COL || ' != ' || 'B.' ||
                    IN_COL;
      FOR I IN 1 .. PRIMARYS_NUM LOOP
        V_COLUMN   := PKG_HXDI_UTIL.F_GETSTR(PRIMARYS, ',', I);
        RESULT_SQL := RESULT_SQL || ' AND A.' || V_COLUMN || ' = ';
        RESULT_SQL := RESULT_SQL || ' B.' || V_COLUMN;
      END LOOP;
    ELSE
      RESULT_SQL := '';
    END IF;
    RETURN RESULT_SQL;
  END;

  /**********************************************************
  #PROCEDURE:：记录日志
  #author:diaoby
  #createdate:2018-12-2 13:00:24
  #input:in_table_name  表名
  #input:in_table_col  字段名
  #input:in_pk_ids   主键ID
  #input:in_pk_values 主键ID值
  #input:in_values10g  字段10g的内容
  #input:in_values12c   字段12c的内容
  #example:
  ***********************************************************/
  PROCEDURE P_RECORDLOG(IN_TABLE_NAME IN VARCHAR2,
                        IN_PK_IDS     IN VARCHAR2,
                        IN_PK_VALUES  IN VARCHAR2,
                        IN_TABLE_COL  IN VARCHAR2,
                        IN_VALUE10G   IN VARCHAR2,
                        IN_VALUE12C   IN VARCHAR2) IS
  
  BEGIN
    INSERT INTO ORACLE_LOG
      (ID_, TABLE_NAME, PK_IDS, PK_VALUES, TABLE_COL, VALUE10G, VALUE12C)
    VALUES
      (SYS_GUID(),
       IN_TABLE_NAME,
       IN_PK_IDS,
       IN_PK_VALUES,
       IN_TABLE_COL,
       IN_VALUE10G,
       IN_VALUE12C);
    COMMIT;
  END;
END PKG_HXDI_ORACLE_UPDATE_UTIL;

CREATE OR REPLACE PACKAGE "PKG_HXDI_ORACLE_UPDATE_UTIL" IS
  TYPE MYTYPE IS REF CURSOR; --游标
  TYPE TABLE_REC IS RECORD(
    TABLE_NAME VARCHAR2(255)); --表名
  TYPE COL_REC IS RECORD(
    CNAME VARCHAR2(255),
    WIDTH NUMBER); --字段
  TYPE V_REC IS RECORD(
    PK_ID     VARCHAR2(2000),
    OLD_VALUE VARCHAR2(4000),
    NEW_VALUE VARCHAR2(4000)); --比对结果值记录，防止有多主键的ID 扩展到主键3个  
  /**********************************************************
  #PROCEDURE:：ORALCE 执行升级比对
  #author:diaoby
  #createdate:2018-12-2 13:00:24
  #example:
  ***********************************************************/
  PROCEDURE P_EXECORACLEUPGRADE;
  /**********************************************************
  #PROCEDURE:：ORALCE 升级VARHCAR2 扩展2倍
  #author:diaoby
  #createdate:2018-12-2 13:00:24
  #example:
  ***********************************************************/
  PROCEDURE P_MODIFYVARCHAR;
  /**********************************************************
  #function:：根据用户账号获取该用户下所有表结构排查记录表，返回游标
  #author:diaoby
  #createdate:2018-12-2 13:00:24
  #input:in_user  用户名
  #example:
  ***********************************************************/
  FUNCTION F_GETALLTABLE(IN_USER IN VARCHAR2) RETURN MYTYPE;

  /**********************************************************
  #function:：根据表名获取所有varhcar2的表字段
  #author:diaoby
  #createdate:2018-12-2 13:00:24
  #input:in_table  表名
  #example:
  ***********************************************************/
  FUNCTION F_GETTABLECOL(IN_TABLE IN VARCHAR2) RETURN MYTYPE;
  /**********************************************************
  #function:：根据表名获取对应主键，可能是联合主键  返回，隔开的varhcar2字段
  #author:diaoby
  #createdate:2018-12-2 13:00:24
  #input:in_table  表名
  #example:
  ***********************************************************/
  FUNCTION F_GETTABLEPRIMARY(IN_TABLE IN VARCHAR2) RETURN VARCHAR2;
  /**********************************************************
  #function:：根据表名,字段名获取执行的sql
  #author:diaoby
  #createdate:2018-12-2 13:00:24
  #input:in_table  表名
  #input:in_col  字段名
  #example:
  ***********************************************************/
  FUNCTION F_GETSQL(IN_TABLE IN VARCHAR2, IN_COL IN VARCHAR2) RETURN VARCHAR2;
  /**********************************************************
  #PROCEDURE:：记录日志
  #author:diaoby
  #createdate:2018-12-2 13:00:24
  #input:in_table_name  表名
  #input:in_table_col  字段名
  #input:in_pk_ids   主键ID
  #input:in_pk_values 主键ID值
  #input:in_values10g  字段10g的内容
  #input:in_values12c   字段12c的内容
  #example:
  ***********************************************************/
  PROCEDURE P_RECORDLOG(IN_TABLE_NAME IN VARCHAR2,
                        IN_PK_IDS     IN VARCHAR2,
                        IN_PK_VALUES  IN VARCHAR2,
                        IN_TABLE_COL  IN VARCHAR2,
                        IN_VALUE10G   IN VARCHAR2,
                        IN_VALUE12C   IN VARCHAR2);
END PKG_HXDI_ORACLE_UPDATE_UTIL;

 

先执行P_EXECORACLEUPGRADE 把所有差异的数据记录到ORACLE_LOG中

后面根据情况执行 P_MODIFYVARCHAR 扩充VARCHAR2