Another finding with ORACLE and Query Tool BETA 20.1.315
I’m trying to fully sync all data sets from Oracle to SQL Server. I have some data entries …that makes the nice and easy
CREATE OR REPLACE table fail.
In my case:
SET invantive-sql-correct-invalid-date@datacontainer true select * from MA_BELEG_KOPF@Oracle_Container
BELEG_FAELLIGKEIT has data type DATE.
But we see that SQL Query tool shows some empty values in the results of
BELEG_FAELLIGKEIT, whereas NULL values are not allowed…
Doing the same statement on ORACLE query tool quickly show what’s wrong: some NULL value showed by SQL Query Tool in fact have values on Oracle.
For this I took two of the records, one with
BELEG_FAELLIGKEIT empty , another filled on Query Tool’s side:
record(BK_ID)#36762 : empty on Query Tool
record(BK_ID)#761 : a date is shown on Query Tool
and on Oracle’s side we have :
as a consequence, doing a
CREATE OR REPLACE table MA_BELEG_KOPF_copied_table@SQLserver_container AS select * from MA_BELEG_KOPF@Oracle_Container
will get a :
Could not bulk insert 3.000 rows into the table SQLserver_container_name.dbo.MA_BELEG_KOPF_copied_table.Column ‘beleg_faelligkeit’ does not allow DBNull.
Of course SQL Server receives NULL values from Query Tool, whereas NULL is not allowed. So SQL Server does not want that.
I think that issue is raised by
SET invantive-sql-correct-invalid-date true not behaving as expected.
set invantive-sql-correct-invalid-date@MM_NAARDEN false select * from MARINA.MA_BELEG_KOPF@MM_NAARDEN WHERE BK_ID = '36762' OR BK_ID = '761'
The value ‘08-12-0007 00:00:00’ of the required column ‘BELEG_FAELLIGKEIT’ in ‘’ is before the start of the Gregorian calendar (01-01-1753).
Any help appreciated. Thanks !