Oracle -- 字符集编码'GBK'库数据导入到'UFT-8'库中 大量报错 ORA-12899 解决方案
需求：
其他项目要求将某环境(字符集编码 'GBK')某用户数据导入到 (字符集编码 'UFT-8')库中.
注释说明：
编码格式转换要注意VARCHAR2的字段类型.
编码格式转换要注意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...即可
--> 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博客 ” ，链接：https://blog.itpub.net/781883/viewspace-1411259/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
