1.问题描述
最近在做Oracle到Postgresql表数据的同步,采用的是datax进行数据迁移,其中几张表同步时总是报如下错误,这个报错困扰了好几天,分享一下解决过程。
2.解决过程
通过以下sql
SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
查询出oracle的编码字符集是ZHS16GBK,而pg库是utf8编码,起初以为是pg库不支持oracle的gbk字符,在百度上找了个oracle字符集转换函数convert(),试了一下,还是报同样的错误。
然后仔细去查看所谓的脏数据,如下图
第一反应,以为是空格的存在,使用trim()函数去出掉空格,查询出来的所谓数据,还是不行,依然报这样的错误。
纠结很久,似乎对这样的数据没有办法。
直到看到这样一条数据
正常情况下,plsql结果集中NULL值,是黄底空白显示,而这个字段是白底空白显示
说明该字段中有记录数据,而非NULL值
当我执行 select length(字段A) from table where id='xxxx'的时候,返回的结果是1,再次证明了这条数据的这个字段看起来是空值,但是其实并不是空值,这儿隐藏了一个陷阱。
试着对其转义:
select ascii(字段A) from table where id='xxxx',结果为0,似乎是chr(0),
对应ASCII编码表中的 NUL (NULL),但解释为空字符,在这里有些说不通,怀疑是换行符,最终也不符合条件,后来经过多方查证,在oracle里面chr(0)表示的是字符串的结束符,这就是chr(0)的陷阱。
3.解决方法
既然找到了脏数据可能存在的原因,那就可以去尝试解决了。
第一种方法:
用navicat,对oracle里面存在这样报错的所谓脏数据表,导出成excel,然后通过navicat导入到pg库,亲测成功,不过具体为什么成功的原因没有去探究。因为这种方式显然不符合我使用datax的初衷,几千条数据的表尚可,上百万条数据的表,用这种方法得累死。放弃
第二种方法:
去替换掉,oracle,表字段里的chr(0)字符,如下
REPLACE (字段A, chr(0), '')
在dataxweb,任务管理中,找寻到表A的任务,编辑,打开构建的json文件,
修改成如下
具体不知道怎么修改的,看我上一篇文章,点击保存,删除掉pg库里表A已经同步的数据,在dataxweb里点击表A的任务,执行一次,即可成功。
如果不确定某张表里,到底哪个字段包含chr(0),可以用以下sql去查询
当然,如果你遇到一张十几年前的表,像我遇到这张表有76个字段的话,默默的含泪埋头干吧。
查询结果里,哪列结果为1,表明这个字段存在数据含有chr(0)
方案:
使用 replace([column_name],chr(0),’’)
或者 replace([column_name],chr(0),null)
第二种方案更符合datax一件同步迁移数据的方式
4.结论
oracle数据中存在 chr(0) 字符 ,
chr(0) 表示的是字符串的结束符
而pg中可能不支持这种字符。