Oracle国产化改造迁移时的问题: Date data type中的 invalid date 0000-00-00(zero year )
我和我的团队近2年一直在做迁移oracle到国产库的项目,数据迁移相对于PLSQL对象兼容改写更加容易,一般迁移工具做好源和目标的data type映射基本就可以,但是有一些情况,如《Oracle国产化改造迁移时的问题: Number data type中的 invalid number》记录的那样无效number,有些数据在oracle中可以存储,但迁移到目标库时可能无法存储,最近我们在迁移一套oracle到postgresql系的国产库时,在date数据类型的列出现了无效日期数据0000年。报错信息
org .postgresql.util.PSQLException: ERRoR: number value does not match the length of the format item.
查看报错数据有些是0000-00-00,数据类型为date. 下面我们演示一下这个问题。
如何写入0000-00-00?
SQL> create table test2(ct date); Table created. SQL> insert into test2 values (sysdate); 1 row created. SQL> insert into test2 values(0); insert into test2 values(0) * ERROR at line 1: ORA-00932: inconsistent datatypes: expected DATE got NUMBER SQL> insert into test2 values(to_date('00000000','yyyymmdd')); insert into test2 values(to_date('00000000','yyyymmdd')) * ERROR at line 1: ORA-01843: An invalid month was specified. SQL> select to_date('0000-00-00','yyyy-mm-dd'); select to_date('0000-00-00','yyyy-mm-dd') * ERROR at line 1: ORA-01843: An invalid month was specified. SQL> SELECT TO_DATE('','YYYYMMDD'); TO_DATE('','YYYYM ----------------- SQL> SELECT to_date ('01-05-0000', 'dd-MM-yyyy'); SELECT to_date ('01-05-0000', 'dd-MM-yyyy') * ERROR at line 1: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 SQL> select to_date(1, 'J'); TO_DATE(1,'J') ----------------- 47120101 00:00:00
Note:
默认这种方式是无法insert的,通过ora-1841也能看出年份的有效范围,及不能为0. 那如何写入呢?
在2012年时写过一篇<还原data block dumps实际值>,当时dump block 的值如何手动转成可视化值,其中DATE类型的dump格式是“年份-100,月日原值,时分秒-1, 再组合”
函数CONVERT_RAW_TO_DATE &CONVERT_RAW_VALUE
在oracle dbms_stats包中有个函数CONVERT_RAW_VALUE 和CONVERT_RAW_TO_DATE
FUNCTION CONVERT_RAW_TO_DATE RETURNS DATE --19c Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- RAWVAL RAW IN PROCEDURE CONVERT_RAW_VALUE --11g Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- RAWVAL RAW IN RESVAL VARCHAR2 OUT
基于上面的时间计算RAW换算方法,’0000-00-00 00:00:00’拼接RAW 后是100,100,0,0,1,1,1, 转换为16进制是64640000010101。
-- 11g CONVERT_RAW_VALUE 创建个自定义function
SQL> create or replace function stats_raw_to_date (p_in raw) return date is
v_date date;
v_char varchar2(25);
begin
dbms_stats.CONVERT_RAW_VALUE(p_in, v_date);
return v_date;
exception
when others then return null;
end;
/
SQL> insert into test2 select sysdate from dual;
SQL> insert into test2 select stats_raw_to_date('64640000010101') x from dual;
1 row created.
SQL> select * from test2;
CT
------------
31-OCT-24
00-DECEMBER-
SQL> alter session set nls_date_format='yyyymmdd hh24:mi:ss';
Session altered.
SQL> select * from test2;
CT
-----------------
20241031 00:04:58
00000000 00:00:00
-- 23c dbms_stats.CONVERT_RAW_TO_DATE
SQL> select dbms_stats.CONVERT_RAW_TO_DATE('64640000010101');
DBMS_STATS.CONVERT_
-------------------
0000-00-00 00:00:00
SQL> insert into test2 select dbms_stats.CONVERT_RAW_TO_DATE('64640000010101');
1 row created.
数据查找
SQL> select * from test2 where ct<to_date('1900-1-1','yyyy-mm-dd'); CT ----------------- 00000000 00:00:00 或者创建一个函数 SQL> create or replace function zero_date return date deterministic is v_date date; begin dbms_stats.CONVERT_RAW_VALUE('64640000010101', v_date); return v_date; end; / SQL> select * from test2 where ct=zero_date; CT ----------------- 00000000 00:00:00 SQL> select dump(ct) dv,ct,dump(ct,16) as rawv from test2 DV CT RAWV -------------------------------------------------- ----------------- ---------------------------------------- Typ=12 Len=7: 120,124,10,31,1,5,59 20241031 00:04:58 Typ=12 Len=7: 78,7c,a,1f,1,5,3b Typ=12 Len=7: 100,100,0,0,1,1,1 00000000 00:00:00 Typ=12 Len=7: 64,64,0,0,1,1,1
zero date 排序无效
SQL> select rownum rn,ct from test2 order by ct desc; RN CT ---------- ----------------- 1 20241031 00:04:58 2 00000000 00:00:00 SQL> select * from ( select rownum rn,ct from test2 order by ct desc ) where rn<2; RN CT ---------- ----------------- 1 20241031 00:04:58 SQL> select * from ( select rownum rn,ct from test2 order by ct asc ) where rn<2; RN CT ---------- ----------------- 1 20241031 00:04:58
注意,除了无法存储的数据外,像年份在<1900 >2100 的数据,虽然date格式有效,但注意也可能是无效的业务数据。
还有更多异构数据迁移问题隐藏在项目中,如果您有国产化改造咨询需求,可以考虑联系我们(www.anbob.com首页的联系方式)
目前这篇文章还没有评论(Rss)