I've a doubt regarding NaT value in Oracle.

I'm using Oracle database and python.

There's a common table between two databases and we're trying to sync it. If there's any mismatch in source and destination table then value should get inserted from source to destination.

There's one column of date type and it is having null values and while getting inserted it from source to destination, the null value is getting inserted as default value rather than null. But we want it as null only. How to resolve this issue?

CodePudding user response：

This is how default works; for the following table (whose name column has the default value set to LF)

SQL> create table test
  2    (id       number,
  3     name     varchar2(10) default 'LF');

Table created.

let's do some inserts. First, specify both columns to be inserted and put a value into one row's name column, and let it be null for another:

SQL> insert into test (id, name)
  2    select 1, 'Little' from dual union all
  3    select 2, null     from dual;

2 rows created.

SQL> select * from test order by id;

        ID NAME
---------- ----------
         1 Little
         2                   --> if you expected LF to be inserted into the
                             --> NAME column, it wasn't. Why? Because you
                             --> explicitly said that you want to put NULL in there

But, if you omit the name column completely:

SQL> insert into test (id) values (3);

1 row created.

SQL> select * from test order by id;

        ID NAME
---------- ----------
         1 Little
         2
         3 LF            --> aha! Now we got the default value here!

SQL>

That's because Oracle did it that way (as described in CREATE TABLE documentation:

The DEFAULT clause lets you specify a value to be assigned to the column if a subsequent INSERT statement omits a value for the column.

There's more to read; have a look, if you want.

What does it mean for you? I don't know, you didn't post code you use to copy data from one table to another.

CodePudding user response：

There's a common table between two databases and we're trying to sync it. If there's any mismatch in source and destination table then value should get inserted from source to destination.

You have not give the table definitions nor your python code but a simple solution is to create a database link from one database to the other and then not use python for the comparison and, instead, use a MERGE statement:

MERGE INTO table_name dst
USING table_name@remote_link src
ON (dst.col1 = src.col1 AND dst.col2 = src.col2 AND dst.col3 = src.col3)
WHEN NOT MATCHED THEN
  INSERT (col1, col2, col3)
  VALUES (src.col1, src.col2, src.col3);