ORA-1400 Cannot Insert NULL in to a NOT NULL column

Installation of Oracle GoldenGate is very easy, but troubleshooting is a challenge. We come across many errors in OGG and some of the famous errors in them are,

ORA-1403 No data found
ORA-1400 Cannot Insert NULL into a NOT NULL column.

There are many reasons for hitting these errors. In this article, I am going to explain you in a diagrammatic way on

how ORA-1400 is been hit?

What exactly happens at the background?

I have a table called EMP with four columns namely, ID, NAME, ADDRESS and SALARY. ID column is the PK column and all are NOT NULL columns.
Minimal supplemental log / Trandata is enabled already. On the Replicat parameter, I am using the parameter INSERTMISSINGUPDATES. To know more about this parameter, check the below link,

https://www.oracle-scn.com/insertmissingupdates-noninsertmissingupdates/

Let me update a row in the source,

UPDATE EMP SET NAME='CHANDAN' WHERE ID=101;

Since only minimal supplemental log is enabled, only the changed columns along with the PK column will be captured by the Extract process.

And also, if you see the image, the row with ID=101 does not exist in the Target table. Since we are using the parameter INSERMISSINGUPDATES in the Replicat process, the current UPDATE will be converted into INSERT. The Replicat process will try to INSERT the missed row into the table.

As I mentioned earlier, only the changed columns along with the PK column will be captured by the Extract process. We have only values for 2 columns ID and NAME. We do not have values for the other two columns ADDRESS and SALARY.

So, the Replicat process will try to INSERT a NULL value to the other two columns ADDRESS and SALARY,

Since the columns are NOT NULL, it will not allow NULL to it and we hit the error, ORA-1400 Cannot insert NULL into a NOT NULL column

The solution for this is,

You need to enable supplemental log on all the columns. This is not only in the case of INSERTMISSINGUPDATES. The below parameters will also lead to this issue,

INSERTUPDATES
INSERTDELETES
HANDLECOLLISIONS
INSERTALLRECORDS

The above parameters are not advised or recommended to use in the replication. This will lead to data integrity issues.

Hope you got a clear idea about the error ORA-1400.

Cheers 🙂