oracle生僻字处理

ZHS16GBK对某些生僻字存储不了，需要把列的类型从varchar2转换成nvarchar2 或者把数据库的字符集转成utf8

对于已提交的数据通过函数utl_raw.cast_to_nvarchar2把unicode转成nvarchar2

对数据操作的时候用unicode转换，根据生僻字到网站查询对应unicode
http://www.bejson.com/convert/unicode_chinese/

测试
update test set ar_address =(select utl_raw.cast_to_nvarchar2('3cc7') from dual);
SELECT ar_address FROM test;

 

 

数据库字符集从zhs16gbk转换成al32utf8

一、导入元数据

二、修改列长

将所有变长字段长度*1.5，所有长度大于1的定长字段修改为varchar2，长度*1.5。
将sql单独执行，发现错误，主要有两种：
其一，字段为系统关键字，需添加单引号
其二，字段长度大于varchar2最大长度4000

查出所有的可能带中文的字段并修改长度：
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MODIFY ("' ||
COLUMN_NAME || '" VARCHAR2(' || CEIL(DATA_LENGTH * 1.5) ||
'));'
FROM DBA_TAB_COLUMNS
WHERE OWNER = 'UAP65'
AND DATA_TYPE IN ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2')
AND DATA_LENGTH > 1
AND COLUMN_NAME not in('SYSDATE','DATE')
AND DATA_LENGTH < 2666
AND TABLE_NAME NOT IN (SELECT VIEW_NAME FROM DBA_VIEWS WHERE OWNER = 'UAP65');

字段较长的字段长度直接修改为4000：
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MODIFY ("' ||
COLUMN_NAME || '" VARCHAR2(4000));'
FROM DBA_TAB_COLUMNS
WHERE OWNER = 'UAP65'
AND DATA_TYPE IN ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2')
AND DATA_LENGTH > 1
AND COLUMN_NAME not in('SYSDATE','DATE')
AND DATA_LENGTH >= 2666
AND TABLE_NAME NOT IN (SELECT VIEW_NAME FROM DBA_VIEWS WHERE OWNER = 'UAP65');

也可直接通过存储过程执行：
(1)对于长度小于2666的，转化后小于varchar2最大长度4000
--SET SERVEROUTPUT ON
--/
declare
sql_ varchar2(2000) ;
begin
for tab in ( select * FROM DBA_TAB_COLUMNS WHERE OWNER = 'UAP65' AND DATA_TYPE IN ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2') AND DATA_LENGTH > 1 AND COLUMN_NAME not in('SYSDATE','DATE') AND DATA_LENGTH < 2666 ) loop
select 'ALTER TABLE ' || tab.OWNER || '.' || tab.TABLE_NAME || ' MODIFY ("' || tab.COLUMN_NAME || '" VARCHAR2(' || CEIL(tab.DATA_LENGTH * 1.5) ||'))' into sql_ from dual ;
--dbms_output.put_line(sql_) ;
execute immediate sql_ ;
end loop ;
end ;
/

（2）对于长度大于2666的，转化后设置为varchar2最大长度4000
--SET SERVEROUTPUT ON
--/
declare
sql_ varchar2(2000) ;
begin
for tab in ( select * FROM DBA_TAB_COLUMNS WHERE OWNER = 'UAP65' AND DATA_TYPE IN ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2') AND DATA_LENGTH > 1 AND COLUMN_NAME not in('SYSDATE','DATE') AND DATA_LENGTH >= 2666 ) loop
select 'ALTER TABLE ' || tab.OWNER || '.' || tab.TABLE_NAME || ' MODIFY ("' || tab.COLUMN_NAME || '" VARCHAR2(4000))' into sql_ from dual ;
--dbms_output.put_line(sql_) ;
execute immediate sql_ ;
end loop ;
end ;
/

 

三、导入数据