ORA-01400 during refresh of MVIEW

 

When trying to refresh a materialized view based on a query that uses a mssql table (using the SqlServer Gateway), I run into the following error:

ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into (%s)
ORA-02063: preceding line from MSSQLDB1
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2537
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2743
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2712

 

After some investigation I found this was caused by a "NOT NULL" constraint on the mview-table.

A small scenario:

STEP 1: Create a Materialized View

SQL> CREATE MATERIALIZED VIEW COUNTY_IS_NULL
2 REFRESH FORCE ON DEMAND
3 AS
4 SELECT a.agreementnum
5 ,a.county county_id
6 ,a.createddate agree_cre_date
7 FROM bmssa.EXU_AGREEMENTTABLE@mssqldb1 a
8 WHERE a.agreementnum = ' E00000001';

 

Materialized view created.

 

STEP 2: View Content of the Materialized View (County_Id is null!)

SQL>
SQL> SELECT * FROM county_is_null;

 

AGREEMENTN COUNTY_ID  AGREE_CRE
---------- ---------- ---------
E00000001 05-JUL-07
 

SQL>
SQL> SELECT NVL(county_id, 0) FROM county_is_null;

NVL(COUNTY
----------
0

 

 STEP 3: Refresh the Materialized View

SQL>
SQL> BEGIN
2 dbms_mview.refresh('COUNTY_IS_NULL');
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into (%s)
ORA-02063: preceding line from MSSQLDB1
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2537
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2743
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2712
ORA-06512: at line 2

 

STEP 4: Disable the NOT NULL Constraint on COUNTY_ID

SQL>
SQL> SELECT constraint_name, search_condition FROM user_constraints c
WHERE c.table_name='COUNTY_IS_NULL';

CONSTRAINT_NAME SEARCH_CONDITION
------------------------------ -------------------------------------------------
SYS_C0029024 "AGREEMENTNUM" IS NOT NULL
SYS_C0029025 "COUNTY_ID" IS NOT NULL
SYS_C0029026 "AGREE_CRE_DATE" IS NOT NULL

 

SQL>
SQL> ALTER TABLE county_is_null MODIFY CONSTRAINT sys_c0029025 DISABLE;
Table altered.

 

STEP 5: Refresh the Materialized View

SQL>
SQL> BEGIN
2 dbms_mview.refresh('COUNTY_IS_NULL');
3 END;
4 /

 

PL/SQL procedure successfully completed.
SQL>

  

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s