ANBOB™

我和我的团队近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首页的联系方式）

打赏

微信扫一扫，打赏作者吧～