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 ;
/
三、导入数据