Oracle -- 字符集编码'GBK'库数据导入到'UFT-8'库中大量报错 ORA-12899 解决方案

需求：
其他项目要求将某环境(字符集编码 'GBK')某用户数据导入到 (字符集编码 'UFT-8')库中.

注释说明：

      编码格式转换要注意VARCHAR2的字段类型.
           --GBK  ：VARCHAR2汉字占2字节，英文1字节 -->ORACLE 默认GBK编码格式
           --UTF-8：VARCHAR2汉字占3字节，英文1字节 -->SELECT USERENV('LANGUAGE') FROM DUAL 或者/V$NLS_PARAMETERS查看ORACLE编码格式
        所以源库中VARCHAR2类型字段的数据导入到UTF- 8(VARCHAR2 (N*1.5))有可能超过GBK VARCHAR2(N)的N长度

      有2个方案 可避免有可能导入的时候报错 'ORA-12899: 列 "TAB"."COLUMN" 的值太大 (实际值: 7, 最大值: 6)' ：
           1）批量将环境(字符集编码'GBK')VARCHAR2类型DDATA_LENGTH全部改成DATA_LENGTH*1.5 
        --> 1.5表示：UTF-8汉字存3字节/GBK汉字存2字节  -->根据user_tab_columns查询写批量alter table...即可
           2）校验类型为VARCHAR2的字段, LENGTHB(字段的最大值)*1.5是否超过VARCHAR2(N)中的N，若超过，将更改表结构ALTER ..为字段的最大数据*1.5..否则报错
        --> 方案2 寓意 ==>  MAX(LENGTHB(COLUMN_NAME)*1.5 是否大于 VARCHAR2(N)里的N值

--> 如下重点介绍和存储校验：

-->--> 2字符集编码间就汉字存储的字节不同，所以可以只考虑汉字部分进行判断
               -->--> 用LENGTH(‘STRING’)!=LENGTHB(‘STRING’)来判断字符串是否含有中文
         -->--> 介绍相关函数：
                --LENGTH FUNCTIONS RETURN THE LENGTH OF CHAR.
                --LENGTH CALCULATES LENGTH USING CHARACTERS AS DEFINED BY THE INPUT CHARACTER SET.  -- 所占字节长度‘单位是字符’
                --LENGTHB USES BYTES INSTEAD OF CHARACTERS.    --文章用到.... 所占字节长度‘单位是字节’
                --LENGTHC USES UNICODE COMPLETE CHARACTERS.
                --LENGTH2 USES UCS2 CODE POINTS.
                --LENGTH4 USES UCS4 CODE POINTS.

 --校验存储....
 --简意：类型为VARCHAR2的字段, LENGTHB(字段的最大值)*1.5是否超过VARCHAR2(N)中的N，若超过，将更改表结构ALTER ..
  CREATE OR REPLACE PROCEDURE CHANGE_VARCHAR2(USERNAME VARCHAR2 ) IS
  --USERNAME 传入要判断的用户
  P_OWNER       VARCHAR2 (4000 );
  P_TABLE_NAME  VARCHAR2 (4000 );
  P_COLUMN_NAME VARCHAR2 (4000 );
  P_DATA_LENGTH NUMBER (30 );
  P_SQL         VARCHAR2 (4000 );
  MAX_NUM       NUMBER (20 ); ---要是写成VARCHAR2(2000)，发现他没有比对，只是将只改成最大，其实就是跳过了比对
  P_SQL_SQL     VARCHAR2 (4000 );

  --创建游标(包含：TABLE_NAME, COLUMN_NAME, DATA_LENGTH)
  CURSOR C_CONS IS
    SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_LENGTH
      FROM DBA_TAB_COLUMNS UTC
     WHERE UTC.OWNER = UPPER (USERNAME)
       AND UTC.DATA_TYPE = 'VARCHAR2'
       AND UTC.TABLE_NAME NOT IN
           ( SELECT TABLE_NAME
              FROM DBA_EXTERNAL_TABLES E
             WHERE E.OWNER = UPPER (USERNAME)) --筛选掉临时表
       AND UTC.TABLE_NAME NOT LIKE 'BIN%' ; --筛选掉回收站表
  -- 编辑存储的用户要有查询DBA_TAB_COLUMNS/DBA_EXTERNAL_TABLES权限；
  -- 没权限会报错：’ERROR: PL/SQL: ORA-00942: 表或视图不存在’
  -- 或者用USER_TAB_COLUMNS/USER_EXTERNAL_TABLES视图避免权限问题..
  -- 授权命令：GRANT SELECT ON DBA_TAB_COLUMNS TO 用户;
  -- 授权命令：GRANT SELECT ON DBA_EXTERNAL_TABLES TO 用户;
BEGIN
  FOR P_C_CONS IN C_CONS LOOP
    P_OWNER       := P_C_CONS.OWNER;
    P_TABLE_NAME  := P_C_CONS.TABLE_NAME;
    P_COLUMN_NAME := P_C_CONS.COLUMN_NAME;
    P_DATA_LENGTH := P_C_CONS.DATA_LENGTH;
    P_SQL         := 'SELECT ROUND((CASE WHEN NVL((MAX(LENGTHB(' ||
                     P_COLUMN_NAME || ')' ||
                     ')*1.5 ),0) =0 THEN 1 ELSE (MAX(LENGTHB(' ||
                     P_COLUMN_NAME || ')' || ')*1.5 ) END))  FROM  ' ||
                     P_OWNER || '.' || P_TABLE_NAME || ' WHERE LENGTH(' ||
                     P_COLUMN_NAME || ')!=LENGTHB(' || P_COLUMN_NAME || ')' ; --查询LENGTHB(字段的最大值)*1.5
 
    --CASE WHEN 加判断是因为 字段可能存在空数据 （报错信息：ORA-06535: EXECUTE IMMEDIATE 中的语句字符串为 NULL 或长度为零）
    --ROUND 若字段存的数据都是NUMBER 且都为1个数字，上面的SQL会为 将列改为1.5（报错：DATA TYPE INCORRECT） 所以四舍五入下..
 
    --DBMS_OUTPUT.PUT_LINE(P_SQL);    -- 若出现问题，打印信息,利于检验为题
    EXECUTE IMMEDIATE P_SQL
      INTO MAX_NUM;
    --DBMS_OUTPUT.PUT_LINE(MAX_NUM);
    IF MAX_NUM > P_DATA_LENGTH /*OR MAX_NUM = P_DATA_LENGTH*/
    --如果LENGTHB(字段的最大值)*1.5的结果 大于 VARCHAR2(N)中的N ，执行alter table改字段长度到（LENGTHB(字段的最大值)*1.5）
     THEN
      P_SQL_SQL := 'ALTER TABLE ' || P_TABLE_NAME || ' MODIFY(' ||
                   P_COLUMN_NAME || ' VARCHAR2(' || MAX_NUM || ')) ' ;
      --DBMS_OUTPUT.PUT_LINE(P_SQL_SQL);  -- 若出现问题，打印信息,利于检验为题
      EXECUTE IMMEDIATE P_SQL_SQL;
    ELSE
      RETURN ;
    END IF ;
  END LOOP ;
END CHANGE_VARCHAR2;

--使用步骤：
--   编辑存储..-->调用存储（Call change_varchar2(username => 'u1' );或者begin...等）

大家是否有疑问，为什么只判断varchar2 不判断char 和nvarchar等类型么？
实验：--字符集编码'GBK' 库中字段类型为CHAR/NVARCHAR2导入到字符集编码'UTF-8'情况 :

SQL > SELECT USERENV( 'LANGUAGE' ) FROM DUAL;
USERENV ('LANGUAGE' )
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
 
SQL > CREATE TABLE T_1(CHA CHAR ( 2 ),NVARCHAR NVARCHAR2 ( 3 ));
 
Table created
SQL > INSERT INTO T_1 (CHA, NVARCHAR) VALUES ( '11' , '111' );
 
1 row inserted
SQL > INSERT INTO T_1 (CHA, NVARCHAR) VALUES ( 'AA' , '111' );
 
1 row inserted
SQL > INSERT INTO T_1 (CHA, NVARCHAR) VALUES ( '中' , '中中中' );
 
1 row inserted
 
SQL > commit ;
 
Commit complete
 
SQL > SELECT * FROM t_1;
 
CHA NVARCHAR
--- --------
11   111
AA  111
中  中中中
 
SQL >
--再在字符集编码'UTF-8'情况
SQL > SELECT USERENV( 'LANGUAGE' ) FROM DUAL;
 
USERENV ('LANGUAGE' )
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.AL32UTF8
 
SQL > create table T_1 as select * from sys.T_1@DB_GBK;
 
Table created
 
SQL > SELECT * FROM t_1;
 
CHA    NVARCHAR
------ --------
11      111
AA     111
中     中中中
SQL >

本文转自ICT时空 dbasdk博客，原文链接：Oracle -- 字符集编码'GBK'库数据导入到'UFT-8'库中大量报错 ORA-12899 解决方案，如需转载请自行联系原博主。

相关文章：