Column conversion error: NULL value found for column that is not nullable

 After registering some tables in a tool that replicates tables from Oracle Database to Snowflake, the tool was failing on a table with the following errors:

PWX-36200 ORAD: Column conversion error: [36202] NULL value found for column that is not nullable for Table name=TABLE01, column[131] segment[131] COLUMN01. Detail Type = INTEGER, Oracle Type = NUMBER.

The first impression for me was that perhaps the column COLUMN01 was set as "NULL" when the table was created. When I checked the column COLUMN01 of the table TABLE01 I saw that the column was set as "NOT NULL".  So the problem should be somewhere else.

I check if perhaps the column COLUMN01 was created as NULL in Snowflake, but after reviewing the target table in Snowflake, I confirmed that the column was set as "NOT NULL". So the root cause was not there.

After perform several investigation, I found that the redo information for tables with more than 255 columns is written in a different way into the archived log files and I suspected that the ETL Tool is not able to understand that redo format while reading the archived log files. I also suspected that "Advanced compression" in columns with more than 255 columns was impacting somehow here because I saw several metalink notes regarding bugs with tables with more than 255, however non of those bugs were related to redolog format.

When I check if the table TABLE01, I confirmed that the table was compressed with "Advanced Compression":

OWNER   TABLE_NAME COMPRESS COMPRESS_FOR
------- ---------- -------- ------------
SCHEMA01 TABLE01    ENABLED ADVANCED


I had to perform the following tests that allowed me to isolate the issue:

• Table with 300 columns and Advance Compression Enabled. UPDATE DML modifying only the column #299= Success.
• Table with 300 columns and Advance Compression Enabled. UPDATE DML modifying only the column #1= Success.
• Table with 300 columns and Advance Compression Enabled. UPDATE DML modifying first 20 columns at a time = Success.
• Table with 300 columns and Advance Compression Enabled. UPDATE DML modifying 270 columns at the time = ERROR. (Null Value on a column that has not null constraint)

So to be specific, the "bug" (to confirm) happens on tables with more than 255 columns, with Advanced Compression enabled, on UPDATE operations where we are modifying more than 255 columns at the same time in the same UPDATE.

Solution:

• Disable Advanced Compression on the table.