[Oracle]ORA-01400 / ORA-06512 에러

Have you ever gotten this error running a dbms_compare in Oracle Streams ?

 

ERROR at line 1:
ORA-01400: cannot insert NULL into
(“LITERATUM_LIVE”.”ALERT_USER_SUBSCRIPTION”.”ADDEDDATE”)
ORA-06512: at “SYS.DBMS_COMPARISON”, line 728
ORA-06512: at line 4

This error usually occurs when trying to run dbms_compare to fix data not aligned with a master table in Oracle Streams.  Basically the “NOT NULL” column was not included in the original dbms_compare program.

The fix is easy to correct, but was difficult to locate in the web.

 

 

 

Modify your dbms_compare script -

Before:

begin
    dbms_comparison.create_comparison(
     COMPARISON_NAME     => ‘AUS_COMPARE_&&1′,
     SCHEMA_NAME         => ‘USER_NAME’,
     OBJECT_NAME         => ‘table_name’,
     DBLINK_NAME         => ‘database link name’,
        MAX_NUM_BUCKETS    => 20000000,
     REMOTE_SCHEMA_NAME => ‘remote user name’,
     REMOTE_OBJECT_NAME => ‘remote user table name’,
     COMPARISON_MODE     =>
         DBMS_COMPARISON.CMP_COMPARE_MODE_OBJECT,
    COLUMN_LIST  => ‘ALERTID’,  <== Missing the NOT NULL Column
    SCAN_MODE    =>
         DBMS_COMPARISON.CMP_SCAN_MODE_FULL);
  end;

 

 

 

FIX:begin
    dbms_comparison.create_comparison(
     COMPARISON_NAME     => ‘AUS_COMPARE_&&1′,
     SCHEMA_NAME         => ‘USER_NAME’,
     OBJECT_NAME         => ‘table_name’,
     DBLINK_NAME         => ‘database link name’,
        MAX_NUM_BUCKETS    => 20000000,
     REMOTE_SCHEMA_NAME => ‘remote user name’,
     REMOTE_OBJECT_NAME => ‘remote user table name’,
     COMPARISON_MODE     =>
         DBMS_COMPARISON.CMP_COMPARE_MODE_OBJECT,
    COLUMN_LIST  => ‘ALERTID, ADDEDDATE’, <=== Column added that was a NOT NULL 
    SCAN_MODE    =>
         DBMS_COMPARISON.CMP_SCAN_MODE_FULL);
  end;

 

 

트리거의 스키마 문제였다. 편집이 불가능해 다 지우고 다시 등록하니 잘됨..