ORA-01407 with Inconsistency between DBMS_METADATA.GET_DDL and DESCRIBE for NULL/NOT NULL of a Table Column (Doc ID 1266816.1)

Last updated on FEBRUARY 24, 2022

Applies to:

Symptoms

You encounter an issue similar to the following example;

SQL> update cust_table set col_level='000',col_allow='' where col_id='XXXXXXXXXXXX' ;
update cust_table set col_level='000',col_allow='' where col_id='XXXXXXXXXXXX'
*
ERROR at line 1:
ORA-01407: cannot update ("SCHEMA_1"."CUST_TABLE"."COL_ALLOW") to NULL

Then try to alter the attribute of the column 'COL_ALLOW':

SQL> alter table cust_table modify ( col_allow not null);
alter table cust_table modify ( col_allow not null)
*
ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is already NOT NULL

Run following command to find the definition of the table
SQL> describe cust_table;
Name Null? Type
------- -------- ----------------------------
COL_ALLOW         CHAR(1)
...

The column should allow NULL according to the "DESCRIBE" output.


DBMS_METADATA.GET_DDL('TABLE','CUST_TABLE','SCHEMA_1')

CREATE TABLE "SCHEMA_1"."CUST_TABLE"
(
...
"COL_ALLOW" CHAR(1) NOT NULL ENABLE NOVALIDATE,
...

So the output of DBMS_METADATA.GET_DDL is inconsistent with the output of the "DESCRIBE" statement.

Cause