How to: Insert Invalid Dates into Oracle and How to Replicate This

We recently came across a situation where the customer was using an ERP system running on top of an Oracle database and needed to replicate some of this data. The ERP is from a large vendor and is widely used in this specific industry. It is one of those ERPs that is advertised as "database independent". This is really just another way of saying: "We are just going to use the database as a dumb storage area for the data". Because they want to be "database independent" all the logic, constraints and business rules are defined in the application. We all know that this is generally not a good idea. The ERP has managed to insert a date of '0000-00-00 00:00:00' into an Oracle DATE field in its tables. This is an invalid date as far as Oracle is concerned. It uses this "date" probably to indicate NULL. This causes all kinds of issues if you want to replicate or select the data. In this case the customers wants to offload the reporting to another server, so the data needed to be replicated and they wanted to use Dbvisit Replicate to replicate the data. I did not know that it was possible to insert an invalid date into an Oracle DATE column, but Vit Spinka our OCM showed me how:

SQL>create table invalid_date (pid number, 
order_number number, 
start_date date, 
end_date date);
declare
   d date;
begin
dbms_stats.convert_raw_value('64640001010100', d);
insert into invalid_date values (101, 200, sysdate, d);
insert into invalid_date values (102, 300, sysdate, sysdate+1);
insert into invalid_date values (103, 400, d, d);
commit;
end;
/

The table is now created and invalid dates have been inserted. To verify we can run a select:

SQL> select to_char(end_date, 'yyyy-dd-mm hh24:mi:ss') END_DATE from invalid_date;
END_DATE
-------------------
0000-00-00 00:00:00
2012-25-08 17:06:13
0000-00-00 00:00:00

We can see that there is an invalid date in the END_DATE column. If we want to select on this we get an error:

SQL> select * from invalid_date where END_DATE = to_date('0000-00-00 00:00:00', 'yyyy-dd-mm hh24:mi:ss');
select * from invalid_date where END_DATE = to_date('0000-00-00 00:00:00', 'yyyy-dd-mm hh24:mi:ss')
                                                    *
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month

You can use the DUMP command to see the hex value of the column:

The customer wants to replicate data that contains this invalid date. But this was not the only issue that they had to deal with. There was also NO primary key (pk) or unique Key (uk). In replication this means that for an UPDATE or DELETE statement the WHERE clause will contain all columns of the table to be able to uniquely identify the row in the table. If the invalid date field is part of the WHERE clause, then this will cause an error:

So how does Dbvisit Replicate deal with invalid dates:

Dbvisit Replicate will replicate the invalid dates as NULL. So the target database will have NULL values instead of invalid.
By having the values as NULL, it means that the update and delete statements will also be replicated without conflicts.

Before replication can start all invalid dates on the target database will have to be updated to NULLS. This is so that there are no conflicts (0 rows updated) if there are UPDATES or DELETES on this data. Here is how you can update the invalid data on your target database: If your table is called XX and your invalid date column is called end_date, then the query is:

Once this invalid data has been updated, the replication can start to keep the source and target database in synch. Interesting to see some real world examples of how some of these systems are implemented and the "real" data that is out there. Anyone have any interesting Oracle data replication stories to tell or seen how other ERP systems "store" the data?