Oracle -- 字符集編碼'GBK'庫資料匯入到'UFT-8'庫中 大量報錯 ORA-12899 解決方案

studywell發表於2017-02-16
Oracle


    需求：
      其他專案要求將某環境(字符集編碼 '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 >


祝好~


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29519108/viewspace-2133622/，如需轉載，請註明出處，否則將追究法律責任。

相關文章