记录我的生活和学习历程

[Oracle] 不同字符集数据库之间的数据(汉字)传输

2012-11-14

前段时间用Oracle ODI同步2个数据库的数据,结果汉字字段全部是乱码,首先想到可能是数据库字符集不对。
执行:
select * from nls_database_parameters where parameter = ‘NLS_CHARACTERSET’;
果然,源数据库是UTF8 ,目标数据库是GBK。
于是开始从编码这块入手,经网上搜索,用oralce 自带的函数 utl_raw.cast_to_raw和utl_raw.cast_to_varchar2这两个函数可以解决。
utl_raw.cast_to_raw是把字符串转成16进制数,utl_raw.cast_to_varchar2是把16进制数转成字符串。
实现思路:
环境:字符集为UTf-8/GBK的数据库A/B中有数据表t_table(id,xm),其中姓名为汉字。
目的:把A库中的t_table 数据同步到B库中的t_table中
1:在A中,创建视图v_table 用utl_raw.cast_to_raw()函数把xm字段转成16进制数
create or replace view v_table as select id,utl_raw.cast_to_raw(xm) xm from t_table;
2:把v_table 中的内容同步到B库中的T_table 中,可以用dblink,也可以用ODI,再次不过多描述。
3:在B中,创建视图v_table 用utl_raw.cast_to_varchar2()函数 把16进制的xm字段转成字符串。
create or replace view v_table as select id,utl_raw.cast_to_varchar2(xm) xm from t_table;
网上看到,做到这一步,好多人都成功了。但是我同步过来的数据,依然是乱码!
经过排查,发现2个问题。
1:utl_raw.cast_to_raw这个函数在不同的字符集数据库环境下,转换同一个字符串结果是不一样的!比如:
在UTF8下 select utl_raw.cast_to_raw(‘刘德华’) from dual;–结果是 E58898E5BEB7E58D8E
在GBK下 select utl_raw.cast_to_raw(‘刘德华’) from dual;–结果是 C1F5B5C2BBAA
2:不同字符集传输ABC字母的时候,也是乱码!
解决思路:
1:convert()用这个函数转换下编码,然后再进行16进制转换。
2:把16进制数据转换成10进制数据然后跨库传输。
操作步骤:
1:在A/B数据库中添加自定义函数,功能:实现二进制、八进制、十进制、十六进制相互转换。内容见附录。
2:更改A库中的视图,pkg_number_trans.f_hex_to_dec() 是自定义函数,把十六进制转成10进制:
create or replace view v_table as
select id,pkg_number_trans.f_hex_to_dec(utl_raw.cast_to_raw(convert(xm,’ZHS16GBK’,’AL32UTF8′))) xm from t_table;
3:把v_table 中的内容同步到B库中的T_table 中。
4:修改B库中的视图。
create or replace view v_table as
select id,utl_raw.cast_to_varchar2(pkg_number_trans.f_dec_to_hex(xm)) xm from t_table;
5:成功!
附录:

CREATE OR REPLACE PACKAGE pkg_number_trans IS 

  FUNCTION f_bin_to_oct(p_str IN VARCHAR2) RETURN VARCHAR2; 
  
  FUNCTION f_bin_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2; 
  
  FUNCTION f_bin_to_hex(p_str IN VARCHAR2) RETURN VARCHAR2; 

  FUNCTION f_oct_to_bin(p_str IN VARCHAR2) RETURN VARCHAR2; 
  
  FUNCTION f_oct_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2; 

  FUNCTION f_oct_to_hex(p_str IN VARCHAR2) RETURN VARCHAR2; 
  
  FUNCTION f_hex_to_bin(p_str IN VARCHAR2) RETURN VARCHAR2; 

  FUNCTION f_hex_to_oct(p_str IN VARCHAR2) RETURN VARCHAR2; 

  FUNCTION f_hex_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2; 
  
  FUNCTION f_dec_to_bin(p_int IN VARCHAR2) RETURN VARCHAR2; 

  FUNCTION f_dec_to_oct(p_int IN VARCHAR2) RETURN VARCHAR2; 

  FUNCTION f_dec_to_hex(p_int IN VARCHAR2) RETURN VARCHAR2; 
  
END pkg_number_trans; 
/ 
CREATE OR REPLACE PACKAGE BODY pkg_number_trans IS 
  FUNCTION f_bin_to_oct(p_str IN VARCHAR2) RETURN VARCHAR2 IS 
    ----------------------------------------------------------------------------------------------
    -- 对象名称: f_bin_to_oct 
    -- 对象描述: 二进制转换八进制 
    -- 输入参数: p_str 二进制字符串 
    -- 返回结果: 八进制字符串 
    -- 测试用例: SELECT pkg_number_trans.f_bin_to_oct('11110001010') FROM dual; 
    -- 备    注: 需要定义f_stragg函数和type_str_agg类型 
    ------------------------------------------------------------------------------------------------
    v_return VARCHAR2(4000); 
    v_bin    VARCHAR2(4000); 
  BEGIN 
    v_bin := substr('00' || p_str, -3 * ceil(length(p_str) / 3)); 
    SELECT f_stragg(data1) INTO v_return 
      FROM (SELECT (CASE upper(substr(v_bin, (rownum - 1) * 3 + 1, 3)) 
                     WHEN '000' THEN '0' 
                     WHEN '001' THEN '1' 
                     WHEN '010' THEN '2' 
                     WHEN '011' THEN '3' 
                     WHEN '100' THEN '4' 
                     WHEN '101' THEN '5' 
                     WHEN '110' THEN '6' 
                     WHEN '111' THEN '7' 
                   END) data1 
              FROM dual 
            CONNECT BY rownum <= length(v_bin) / 3); 
    RETURN v_return; 
  EXCEPTION 
    WHEN OTHERS THEN 
      RETURN NULL; 
  END f_bin_to_oct; 

  FUNCTION f_bin_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS 
    ---------------------------------------------------------------------------------------- 
    -- 对象名称: f_bin_to_dec 
    -- 对象描述: 二进制转换十进制 
    -- 输入参数: p_str 二进制字符串 
    -- 返回结果: 十进制字符串 
    -- 测试用例: SELECT pkg_number_trans.f_bin_to_dec('11110001010') FROM dual; 
    ----------------------------------------------------------------------------------------- 
    v_return  VARCHAR2(4000); 
  BEGIN 
    SELECT SUM(data1) INTO v_return 
      FROM (SELECT substr(p_str, rownum, 1) * power(2, length(p_str) - rownum) data1 
              FROM dual 
            CONNECT BY rownum <= length(p_str)); 
    RETURN v_return; 
  EXCEPTION 
    WHEN OTHERS THEN 
      RETURN NULL; 
  END f_bin_to_dec; 

  FUNCTION f_bin_to_hex(p_str IN VARCHAR2) RETURN VARCHAR2 IS 
    ----------------------------------------------------------------------------------- 
    -- 对象名称: f_bin_to_hex 
    -- 对象描述: 二进制转换十六进制 
    -- 输入参数: p_str 二进制字符串 
    -- 返回结果: 十六进制字符串 
    -- 测试用例: SELECT pkg_number_trans.f_bin_to_oct('11110001010') FROM dual; 
    -- 备    注: 需要定义f_stragg函数和type_str_agg类型 
    -------------------------------------------------------------------------------------- 
    v_return VARCHAR2(4000); 
    v_bin    VARCHAR2(4000); 
  BEGIN 
    v_bin := substr('000' || p_str, -4 * ceil(length(p_str) / 4)); 
    SELECT f_stragg(data1) INTO v_return 
      FROM (SELECT (CASE upper(substr(v_bin, (rownum - 1) * 4 + 1, 4)) 
                     WHEN '0000' THEN '0' 
                     WHEN '0001' THEN '1' 
                     WHEN '0010' THEN '2' 
                     WHEN '0011' THEN '3' 
                     WHEN '0100' THEN '4' 
                     WHEN '0101' THEN '5' 
                     WHEN '0110' THEN '6' 
                     WHEN '0111' THEN '7' 
                     WHEN '1000' THEN '8' 
                     WHEN '1001' THEN '9' 
                     WHEN '1010' THEN 'A' 
                     WHEN '1011' THEN 'B' 
                     WHEN '1100' THEN 'C' 
                     WHEN '1101' THEN 'D' 
                     WHEN '1110' THEN 'E' 
                     WHEN '1111' THEN 'F' 
                   END) data1 
              FROM dual 
            CONNECT BY rownum <= length(v_bin) / 4); 
    RETURN v_return; 
  EXCEPTION 
    WHEN OTHERS THEN 
      RETURN NULL; 
  END f_bin_to_hex; 

  FUNCTION f_oct_to_bin(p_str IN VARCHAR2) RETURN VARCHAR2 IS 
    ------------------------------------------------------------------------------------------- 
    -- 对象名称: f_oct_to_bin 
    -- 对象描述: 八进制转换二进制 
    -- 输入参数: p_str 八进制字符串 
    -- 返回结果: 二进制字符串 
    -- 测试用例: SELECT pkg_number_trans.f_oct_to_bin('3612') FROM dual; 
    -- 备    注: 需要定义f_stragg函数和type_str_agg类型 
    -------------------------------------------------------------------------------------------- 
    v_return VARCHAR2(4000); 
  BEGIN 
    SELECT to_char(to_number(f_stragg(data1))) INTO v_return 
      FROM (SELECT (CASE upper(substr(p_str, rownum, 1)) 
                     WHEN '0' THEN '000' 
                     WHEN '1' THEN '001' 
                     WHEN '2' THEN '010' 
                     WHEN '3' THEN '011' 
                     WHEN '4' THEN '100' 
                     WHEN '5' THEN '101' 
                     WHEN '6' THEN '110' 
                     WHEN '7' THEN '111' 
                   END) data1 
              FROM dual 
            CONNECT BY rownum <= length(p_str)); 
    RETURN v_return; 
  EXCEPTION 
    WHEN OTHERS THEN 
      RETURN NULL; 
  END f_oct_to_bin; 

  FUNCTION f_oct_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS 
    ---------------------------------------------------------------------------------------------------------------------- 
    -- 对象名称: f_oct_to_dec 
    -- 对象描述: 八进制转换十进制 
    -- 输入参数: p_str 八进制字符串 
    -- 返回结果: 十进制字符串 
    -- 测试用例: SELECT pkg_number_trans.f_oct_to_dec('3612') FROM dual; 
    ---------------------------------------------------------------------------------------------------------------------- 
    v_return  VARCHAR2(4000); 
  BEGIN 
    SELECT SUM(data1) INTO v_return 
      FROM (SELECT substr(p_str, rownum, 1) * power(8, length(p_str) - rownum) data1 
              FROM dual 
            CONNECT BY rownum <= length(p_str)); 
    RETURN v_return; 
  EXCEPTION 
    WHEN OTHERS THEN 
      RETURN NULL; 
  END f_oct_to_dec; 
  
  FUNCTION f_oct_to_hex(p_str IN VARCHAR2) RETURN VARCHAR2 IS 
    --------------------------------------------------------------------------------------------- 
    -- 对象名称: f_oct_to_bin 
    -- 对象描述: 八进制转换十六进制 
    -- 输入参数: p_str 八进制字符串 
    -- 返回结果: 十六进制字符串 
    -- 测试用例: SELECT pkg_number_trans.f_oct_to_hex('3612') FROM dual; 
    ---------------------------------------------------------------------------------------------- 
    v_return VARCHAR2(4000); 
    v_bin    VARCHAR2(4000); 
  BEGIN 
    SELECT pkg_number_trans.f_oct_to_bin(p_str) INTO v_bin FROM dual; 
    SELECT pkg_number_trans.f_bin_to_hex(v_bin) INTO v_return FROM dual; 
    RETURN v_return; 
  EXCEPTION 
    WHEN OTHERS THEN 
      RETURN NULL; 
  END f_oct_to_hex; 

  FUNCTION f_dec_to_bin(p_int IN VARCHAR2) RETURN VARCHAR2 IS 
    ---------------------------------------------------------------------------------------------------------------------- 
    -- 对象名称: f_dec_to_bin 
    -- 对象描述: 十进制转换二进制 
    -- 输入参数: p_str 十进制字符串 
    -- 返回结果: 二进制字符串 
    -- 测试用例: SELECT pkg_number_trans.f_dec_to_bin('1930') FROM dual; 
    ---------------------------------------------------------------------------------------------------------------------- 
    v_return VARCHAR2(4000); 
    v_hex    VARCHAR2(4000); 
  BEGIN 
    SELECT pkg_number_trans.f_dec_to_hex(p_int) INTO v_hex FROM dual; 
    SELECT pkg_number_trans.f_hex_to_bin(v_hex) INTO v_return FROM dual; 
    RETURN v_return; 
  EXCEPTION 
    WHEN OTHERS THEN 
      RETURN NULL; 
  END f_dec_to_bin; 
  
  FUNCTION f_dec_to_oct(p_int IN VARCHAR2) RETURN VARCHAR2 IS 
    ---------------------------------------------------------------------------------------------------------------------- 
    -- 对象名称: f_dec_to_oct 
    -- 对象描述: 十进制转换八进制 
    -- 输入参数: p_str 十进制字符串 
    -- 返回结果: 八进制字符串 
    -- 测试用例: SELECT pkg_number_trans.f_dec_to_oct('1930') FROM dual; 
    ---------------------------------------------------------------------------------------------------------------------- 
    v_return VARCHAR2(4000); 
    v_bin    VARCHAR2(4000); 
  BEGIN 
    SELECT pkg_number_trans.f_dec_to_bin(p_int) INTO v_bin FROM dual; 
    v_bin := substr('00' || v_bin, -3 * ceil(length(v_bin) / 3)); 
    SELECT f_stragg(data1) INTO v_return 
      FROM (SELECT (CASE upper(substr(v_bin, (rownum - 1) * 3 + 1, 3)) 
                     WHEN '000' THEN '0' 
                     WHEN '001' THEN '1' 
                     WHEN '010' THEN '2' 
                     WHEN '011' THEN '3' 
                     WHEN '100' THEN '4' 
                     WHEN '101' THEN '5' 
                     WHEN '110' THEN '6' 
                     WHEN '111' THEN '7' 
                   END) data1 
              FROM dual 
            CONNECT BY rownum <= length(v_bin) / 3); 
    RETURN v_return; 
  EXCEPTION 
    WHEN OTHERS THEN 
      RETURN NULL; 
  END f_dec_to_oct; 
  
  FUNCTION f_dec_to_hex(p_int IN VARCHAR2) RETURN VARCHAR2 IS 
    ---------------------------------------------------------------------------------------------------------------------- 
    -- 对象名称: f_dec_to_oct 
    -- 对象描述: 十进制转换十六进制 
    -- 输入参数: p_str 十进制字符串 
    -- 返回结果: 十六进制字符串 
    -- 测试用例: SELECT pkg_number_trans.f_dec_to_hex('1930') FROM dual; 
    ---------------------------------------------------------------------------------------------------------------------- 
    v_return VARCHAR2(4000); 
  BEGIN 
    SELECT upper(TRIM(to_char(p_int, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'))) INTO v_return FROM dual; 
    RETURN v_return; 
  EXCEPTION 
    WHEN OTHERS THEN 
      RETURN NULL; 
  END f_dec_to_hex; 
  
  FUNCTION f_hex_to_bin(p_str IN VARCHAR2) RETURN VARCHAR2 IS 
    ---------------------------------------------------------------------------------------------------------------------- 
    -- 对象名称: f_dec_to_oct 
    -- 对象描述: 十六进制转换二进制 
    -- 输入参数: p_str 十六进制字符串 
    -- 返回结果: 二进制字符串 
    -- 测试用例: SELECT pkg_number_trans.f_hex_to_oct('78A') FROM dual; 
    ---------------------------------------------------------------------------------------------------------------------- 
    v_return VARCHAR2(4000); 
  BEGIN 
    SELECT to_char(to_number(f_stragg(data1))) INTO v_return 
      FROM (SELECT (CASE upper(substr(p_str, rownum, 1)) 
                     WHEN '0' THEN '0000' 
                     WHEN '1' THEN '0001' 
                     WHEN '2' THEN '0010' 
                     WHEN '3' THEN '0011' 
                     WHEN '4' THEN '0100' 
                     WHEN '5' THEN '0101' 
                     WHEN '6' THEN '0110' 
                     WHEN '7' THEN '0111' 
                     WHEN '8' THEN '1000' 
                     WHEN '9' THEN '1001' 
                     WHEN 'A' THEN '1010' 
                     WHEN 'B' THEN '1011' 
                     WHEN 'C' THEN '1100' 
                     WHEN 'D' THEN '1101' 
                     WHEN 'E' THEN '1110' 
                     WHEN 'F' THEN '1111' 
                   END) data1 
              FROM dual 
            CONNECT BY rownum <= length(p_str)); 
    RETURN v_return; 
  EXCEPTION 
    WHEN OTHERS THEN 
      RETURN NULL; 
  END f_hex_to_bin; 
  
  FUNCTION f_hex_to_oct(p_str IN VARCHAR2) RETURN VARCHAR2 IS 
    ---------------------------------------------------------------------------------------------------------------------- 
    -- 对象名称: f_dec_to_oct 
    -- 对象描述: 十六进制转换八进制 
    -- 输入参数: p_str 十六进制字符串 
    -- 返回结果: 八进制字符串 
    -- 测试用例: SELECT pkg_number_trans.f_hex_to_oct('78A') FROM dual; 
    ---------------------------------------------------------------------------------------------------------------------- 
    v_return VARCHAR2(4000); 
    v_bin    VARCHAR2(4000); 
  BEGIN 
    SELECT pkg_number_trans.f_hex_to_bin(p_str) INTO v_bin FROM dual; 
    SELECT pkg_number_trans.f_bin_to_oct(v_bin) INTO v_return FROM dual; 
    RETURN v_return; 
  EXCEPTION 
    WHEN OTHERS THEN 
      RETURN NULL; 
  END f_hex_to_oct; 
  
  FUNCTION f_hex_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS 
    ---------------------------------------------------------------------------------------------------------------------- 
    -- 对象名称: f_hex_to_dec 
    -- 对象描述: 十六进制转换十进制 
    -- 输入参数: p_str 十六进制字符串 
    -- 返回结果: 十进制字符串 
    -- 测试用例: SELECT pkg_number_trans.f_hex_to_dec('78A') FROM dual; 
    ---------------------------------------------------------------------------------------------------------------------- 
    v_return  VARCHAR2(4000); 
  BEGIN 
    SELECT SUM(data1) INTO v_return 
      FROM (SELECT (CASE upper(substr(p_str, rownum, 1)) 
                     WHEN 'A' THEN '10' 
                     WHEN 'B' THEN '11' 
                     WHEN 'C' THEN '12' 
                     WHEN 'D' THEN '13' 
                     WHEN 'E' THEN '14' 
                     WHEN 'F' THEN '15' 
                     ELSE substr(p_str, rownum, 1) 
                   END) * power(16, length(p_str) - rownum) data1 
              FROM dual 
            CONNECT BY rownum <= length(p_str)); 
    RETURN v_return; 
  EXCEPTION 
    WHEN OTHERS THEN 
      RETURN NULL; 
  END f_hex_to_dec; 
  
END pkg_number_trans; 
/ 

CREATE OR REPLACE TYPE type_str_agg AS OBJECT 
( 
  total VARCHAR2(4000), 

  STATIC FUNCTION odciaggregateinitialize(sctx IN OUT type_str_agg) 
    RETURN NUMBER, 

  MEMBER FUNCTION odciaggregateiterate 
  ( 
    SELF  IN OUT type_str_agg, 
    VALUE IN VARCHAR2 
  ) RETURN NUMBER, 

  MEMBER FUNCTION odciaggregateterminate 
  ( 
    SELF        IN type_str_agg, 
    returnvalue OUT VARCHAR2, 
    flags       IN NUMBER 
  ) RETURN NUMBER, 

  MEMBER FUNCTION odciaggregatemerge 
  ( 
    SELF IN OUT type_str_agg, 
    ctx2 IN type_str_agg 
  ) RETURN NUMBER 
) 
/ 
CREATE OR REPLACE TYPE BODY type_str_agg IS 

  STATIC FUNCTION odciaggregateinitialize(sctx IN OUT type_str_agg) 
    RETURN NUMBER IS 
  BEGIN 
    sctx := type_str_agg(NULL); 
    RETURN odciconst.success; 
  END; 

  MEMBER FUNCTION odciaggregateiterate 
  ( 
    SELF  IN OUT type_str_agg, 
    VALUE IN VARCHAR2 
  ) RETURN NUMBER IS 
  BEGIN 
    SELF.total := SELF.total || VALUE; 
    RETURN odciconst.success; 
  END; 

  MEMBER FUNCTION odciaggregateterminate 
  ( 
    SELF        IN type_str_agg, 
    returnvalue OUT VARCHAR2, 
    flags       IN NUMBER 
  ) RETURN NUMBER IS 
  BEGIN 
    returnvalue := SELF.total; 
    RETURN odciconst.success; 
  END; 

  MEMBER FUNCTION odciaggregatemerge 
  ( 
    SELF IN OUT type_str_agg, 
    ctx2 IN type_str_agg 
  ) RETURN NUMBER IS 
  BEGIN 
    SELF.total := SELF.total || ctx2.total; 
    RETURN odciconst.success; 
  END; 
END; 
/ 

CREATE OR REPLACE FUNCTION f_stragg(p_input VARCHAR2) RETURN VARCHAR2 
  PARALLEL_ENABLE 
  AGGREGATE USING type_str_agg; 
/ 
作者:swappy | Categories:Oracle | Tags:

发表评论

电子邮件地址不会被公开。 必填项已用*标注