Strange behaviour with data-type dates on Oracle resulting on failing to get the field value due to SET invantive-sql-correct-invalid-date

[sda]

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

works fine.

The column 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.
doing:

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'

gets a

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 !

[forums]

Thank you for the clear explanation. It seems that this Oracle standard package stores dates still as relative to the year 1 instead to the current century. That is quite ancient and was late 1996 or so the cause of the introduction of the date format “RRRR” on Oracle.

To avoid data loss, the recommended approach is to manually create a statement which corrects the data. The default invalid date correction of Invantive SQL has no support for year 2000 issues.

However, this scenario also shows an error in the default date correction mechanism of Invantive SQL. It should replace the invalid dates by 1-1-1970 instead of null when the data type requires a value. I will discuss this problem internal.

[sda]

Thanks for you reply.
I think there is something more… let me explain:
when the date is 13-12-02 on Oracle , invantive-sql-correct-invalid-date output is 13-12-2002
when the date is 08-12-07 on Oracle, invantive-sql-correct-invalid-date output is NULL
that means there is some kind of support for year 2000 issues…
Out of 130K records, invantive-sql-correct-invalid-date corrects most of them…

And I have an other table where invantive-sql-correct-invalid-date works fine for 25K records of YY dates

Because I use a full copy script (parsing the table.schema), with over 130 tables and tons of columns, it is difficult…
How can I say that each time there is a date data-type, check if the format is YY or YYYY (but we can assume that YY will be everywhere) and then make a TO_DATE('DD-MM-YY') to this field?

Here is the script that I use (copyright @gls)

    set invantive-sql-correct-invalid-date@MM_NAARDEN true

    declare
    --
    -- Alias of source data container.
    -- In this case Oracle RDBMS.
    --
    l_source_alias varchar2 := 'MM_NAARDEN';
    --
    -- Alias of target data container.
    -- In this case SQL Server 2019 instance.
    --
    l_target_alias varchar2 := 'sql2019';
    begin
    for r
    in
    ( select 'create or replace table naarden_'
    || lower(ste.name)
    || '@'
    || l_target_alias
    || ' as select * from '
    || ste.schema
    || '.'
    || ste.name
    || '@'
    || ste.provider_data_container_alias
    stmt
    from SYSTEMTABLES@DataDictionary ste
    where ste.provider_data_container_alias = l_source_alias
    and ste.schema = 'MARINA'
    order
    by ste.name
    )
    loop
    execute immediate r.stmt;
    end loop;
    end;

[forums]

On Oracle, all dates are stored as a 7-byte array with second resolution. There is no actual storage difference between year within century or year. The correct format to use therefor is always ‘YYYY’ or ‘RRRR’ (this last one is only supported by Oracle, not by Invantive SQL, and does year 2000 handling).

Can you perform an analysis as follows directly on Oracle:

select to_char(COLUMN, 'YYYYMMDD') t
,      count(*)
from   tab
group
by     to_char(COLUMN, 'YYYYMMDD')

This should be executed on Oracle natively.

And then on Invantive SQL with invantive-sql-correct-invalid-date set to true:

select t
,      COLUMN
,      count(*)
from   ( select to_char(COLUMN, 'YYYYMMDD') t
         ,      COLUMN
         from   tab
       )
group
by     t
,      COLUMN

And then share the outcomes?

Also, please press F4 in the Query Tool on the table involved and check that the columns are actually correctly determined as nullable or not, as visible by variations in the column “Optional” in this picture taken from an Oracle data source:

Test case

The following case has been used on Oracle to test:

On Oracle:

create table testnull
( dreq date null
, dopt date not null
)

insert into testnull(dreq, dopt) values (trunc(sysdate), trunc(sysdate));

insert into testnull(dreq, dopt) values (to_date('14000101', 'YYYYMMDD'), to_date('14000101', 'YYYYMMDD'));

commit

On Invantive SQL:

set invantive-sql-correct-invalid-date@ora false

select * from testnull@ora

raises:

The value ‘01-01-1400 00:00:00’ of the optional column ‘DREQ’ in ‘’ is before the start of the Gregorian calendar (01-01-1753).

Then:

set invantive-sql-correct-invalid-date@ora true

select * from testnull@ora

returns the expected result:

[sda]

@forums

invantive-sql-correct-invalid

Here are the results. If I understand what you are trying to achieve, invantive-sql-correct-invalid-date gives a correct output on 98,57% of the on the 136328 records.

Also the column BELEG_faelligkeit appears to be not nullable in the options.

[sda]

More interesting fact: this dates are indeed invalid, before the Gregorian calendar.

15-11-07 = 15-11-0007 or 15-11-2007

Then, if I’m not mistaken, the fix should be : invantive-sql-correct-invalid-date giving 01-01-1753 because the field is not allowed as NULL.

[forums]

Interesting and also intriguing. The Excel shows that there are 28 entries in Oracle in the good wine years around the birth of Jezus Christ (according to a popular Dutch song), and the thousands of entries in 2002 and later. However, the column BELEG_FAELLIGKEIT is a required column shown in the Describe of the table in Invantive Query Tool. The values should then be mapped to 01-01-1753 as you stated, but are instead mapped to NULL. This is either a bug or some very specific situation in the environment I don’t see at the moment.

I recommend to schedule an appointment using a consult on 3rd line support. When it is found to be a bug in Invantive SQL, the time will be covered by the subscription. Otherwise, regular consulting fees apply. Analysis probably takes 30 minutes.

Have a great holiday season!

[forums]

Checked using native ADO.NET query:

select *
from   Database.Metadata.Columns@MM_NAARDEN
where  owner      = 'MARINA'
and    table_name = 'MA_BELEG_KOPF'
and    datatype   = 'DATE'

The column is indeed required and correctly translated into an Invantive SQL table. However, the translation of an invalid date in a required column into a Gregorian has a bug. This will be fixed in the BETA released today.

[forums]

The following adapted logic (untested) makes the logic more tolerant against individual copies of a table failing. It copies all tables from Oracle to SQL Server, and at the end raises a (possibly very long) error message for all statement that have failed:

set invantive-sql-correct-invalid-date@MM_NAARDEN true

declare
  --
  -- Alias of source data container.
  -- In this case Oracle RDBMS.
  --
  l_source_alias varchar2 := 'MM_NAARDEN';
  --
  -- Alias of target data container.
  -- In this case SQL Server 2019 instance.
  --
  l_target_alias varchar2 := 'sql2019';
  --
  -- Cumulative errors.
  --
  l_error_txt       varchar2;
  l_error_cnt       pls_integer;
begin
  l_error_txt := '';
  l_error_cnt := 0;
  --
  for r
  in
  ( select 'create or replace table naarden_'
           || lower(ste.name)
           || '@'
           || l_target_alias
           || ' as select * from '
           || ste.schema
           || '.'
           || ste.name
           || '@'
           || ste.provider_data_container_alias
           stmt
    from   SYSTEMTABLES@DataDictionary ste
    where  ste.provider_data_container_alias = l_source_alias
    and    ste.schema = 'MARINA'
    order
    by     ste.name
  )
  loop
    begin
      execute immediate r.stmt;
    exception
      when others
      then
        l_error_txt := l_error_txt 
                       || chr(13) || chr(10) 
                       || 'Statement ' 
                       || r.stmt 
                       || ':' 
                       || sqlerrm
                       ;
        l_error_cnt := l_error_cnt + 1;
    end;
  end loop;
  --
  if l_error_cnt > 0
  then
    raise_application_error(-20163, l_error_txt);
  end if;
end;