ORA-1400 On Materialized View Fast Refresh With Not Null Clob

(Doc ID 733027.1)

Last updated on JUNE 22, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.1.0.6 [Release 9.2 to 11.1]
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.

Symptoms

-- Problem Statement:
Master Table does have a NOT NULL CLOB Column and there is a Materialized View Log defined on the Master Table

SQL> desc PHIS_GEN_LETTER_TEMPLATES
Name Null? Type
----------------------------------------- -------- ---------------------------
ID NOT NULL NUMBER(15)
GHA_ID NOT NULL NUMBER(15)
NAME_L1 NOT NULL VARCHAR2(100)
NAME_L2 VARCHAR2(100)
DESCRIPTION_L1 VARCHAR2(500)
DESCRIPTION_L2 VARCHAR2(500)
GCL_PROGRAM_AREA_CD NOT NULL NUMBER(15)
GCL_CD_TYPE NUMBER(15)
GICD_CODE VARCHAR2(10)
GCL_RECIPIENT_TYPE NUMBER(15)
TEMPLATE_BODY_L1 NOT NULL CLOB
TEMPLATE_BODY_L2 CLOB
SIGNATURE_LINE_L1 VARCHAR2(1000)
SIGNATURE_LINE_L2 VARCHAR2(1000)
GLI_HEADER NUMBER(15)
GLI_FOOTER NUMBER(15)
GLI_SIGNATURE NUMBER(15)
ACTIVE_IND NOT NULL VARCHAR2(1)
WHEN_CREATED NOT NULL DATE
WHO_CREATED NOT NULL NUMBER(15)
WHEN_UPDATED NOT NULL DATE
WHO_UPDATED NOT NULL NUMBER(15)

A Distributed Materialized View is created as select * from Master Table and FAST Refreshable.

Fast Refresh fails with:

ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into ("PHIS"."PHIS_GEN_LETTER_TEMPLATES"."TEMPLATE_BODY_L1")

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms