10 Replies Latest reply: Apr 2, 2014 6:20 AM by BluShadow RSS

    ORA-01841 error while refreshing Materialized views

    user1758538

      Hi All,

       

      Please can someone help me out where the problem could be.

      Please note I have already gone through the post ORA-01841 when refreshing materialized view which sounds very similar but the problem is different.

      We are using a Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production database.

       

      Problem statement:

      Currently all mview refresh statements using "exec dbms_mview.refresh('MVIEW_NAME')" are failing with the following error:

       

      exec dbms_mview.refresh('MVIEW_NAME')

      Error report -

      ORA-01841: (full) year must be between -4713 and +9999, and not be 0

      ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563

      ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776

      ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2745

      ORA-06512: at line 1

      01841. 00000 -  "(full) year must be between -4713 and +9999, and not be 0"

      *Cause:    Illegal year entered

      *Action:   Input year in the specified range

       

      However these used to work fine till last week and in the other environments like development UAT and Prod these still work fine.

       

      There haven't been any code changes to these mviews recently.

       

      There are no date arithmatic or date conversion operation. Neither is any join being done on the date type columns.

      I haven't been able to figure out what could be causing this and what should be done to resolve the issue.

       

      I have checked the NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT which match across the different environments.

      NLS_DATE_FORMAT :: DD-MON-RR

      NLS_TIMESTAMP_FORMAT :: DD-MON-RR HH.MI.SSXFF AM

      Any pointers to help further investigation and resolution.

       

      Regards

      Alok

        • 1. Re: ORA-01841 error while refreshing Materialized views
          BluShadow

          Post details of the underlying table structures and the SQL of the Materialized View.

           

          We can't help fix a problem without seeing the code.

           

          Re: 2. How do I ask a question on the forums?

          • 2. Re: ORA-01841 error while refreshing Materialized views
            Moazzam

            Is this pre-built table type MV. Are there any triggers associated with the table?

            • 3. Re: ORA-01841 error while refreshing Materialized views
              user1758538

              Here's the m-view ddl and the desc o/p for the underlying tables used.

               

              CREATE MATERIALIZED VIEW WF_ACT_FACT_WAF_PLACMNT_CUR_MV (
                PLCMNT_CUR_ACT_FACT_KEY_OPEN, PLCMNT_CUR_ACT_FACT_KEY_CLOSED,
                PLCMNT_CUR_ACTIVITY_NK, PLCMNT_CUR_EVENT_KEY,
                PLCMNT_CUR_ACT_BUS_ASSOC_KEY, PLCMNT_CUR_ACT_BA_ROLE_TYP_KEY,
                PLCMNT_CUR_ACT_STATUS_TYPE_KEY, PLCMNT_CUR_ACT_OPEN_DATE_KEY,
                PLCMNT_CUR_ACT_CLOSED_DATE_KEY, PLCMNT_CUR_ACT_OPEN_TIME_KEY,
                PLCMNT_CUR_ACT_CLOSED_TIME_KEY, PLCMNT_CUR_ACT_TYPE_KEY,
                PLCMNT_CUR_ACT_CLASS_TYPE_KEY, PLCMNT_CUR_ACT_TGT_STT_TYP_KEY,
                PLCMNT_CUR_ACT_TGT_DATE_KEY, PLCMNT_CUR_ACT_COLLEAGUE_KEY,
                PLCMNT_CUR_ACT_ALOC_COLL_KEY, PLCMNT_CUR_ACT_REF,
                PLCMNT_CUR_ACT_ALT1_REF, PLCMNT_CUR_ACT_ALT2_REF,
                PLCMNT_CUR_ACT_COMMENTS_TX, PLCMNT_CUR_ACT_RSN1_TYPE_KEY,
                PLCMNT_CUR_ACT_RSN2_TYPE_KEY, PLCMNT_CUR_ACT_ALOC_TEAM_KEY,
                PLCMNT_CUR_DUE_DAYS_NR, PLCMNT_CUR_SLA_DAYS_NR, PLCMNT_CUR_BAS_DIM_KEY,
                PLCMNT_CUR_BTD_DIM_KEY, PLCMNT_CUR_RID_DIM_KEY,
                PLCMNT_CUR_ROW_SRC_SYS_KEY, PLCMNT_CUR_ROW_SRC_CNTRY_KEY,
                PLCMNT_CUR_ROW_INSERT_DT, PLCMNT_CUR_ROW_INSERT_USER_TX,
                PLCMNT_CUR_ROW_INSERT_LOAD_NR, PLCMNT_CUR_ROW_INSERT_BATCH_NR,
                PLCMNT_CUR_ROW_LTST_UPD_DT, PLCMNT_CUR_ROW_LTST_UPD_USR_TX,
                PLCMNT_CUR_ROW_LTST_UPD_BATCH, PLCMNT_CUR_ROW_LTST_UPD_LOAD,
                PLCMNT_CUR_ROW_SRC_SYS_DEL_FL)
                REFRESH FORCE ON DEMAND
              AS
                SELECT
                  WAF_OPEN.WF_ACTIVITY_FACT_KEY,
                  WAF_CLOSED.WF_ACTIVITY_FACT_KEY,
                  WAF_OPEN.WF_ACTIVITY_NK,
                  WAF_OPEN.WF_EVENT_KEY,
                  WAF_OPEN.WF_ACTIVITY_BUS_ASSOC_KEY,
                  WAF_OPEN.WF_ACTIVITY_BA_ROLE_TYPE_KEY,
                  NVL(WAF_CLOSED.WF_ACTIVITY_STATUS_TYPE_KEY,
                  WAF_OPEN.WF_ACTIVITY_STATUS_TYPE_KEY),
                  WAF_OPEN.WF_ACTIVITY_DATE_KEY,
                  NVL(WAF_CLOSED.WF_ACTIVITY_DATE_KEY,-3),
                  WAF_OPEN.WF_ACTIVITY_TIME_KEY,
                  NVL(WAF_CLOSED.WF_ACTIVITY_TIME_KEY,-3),
                  WAF_OPEN.WF_ACTIVITY_TYPE_KEY,
                  WAF_OPEN.WF_ACTIVITY_CLASS_TYPE_KEY,
                  WAF_OPEN.WF_ACT_TARGET_STATUS_TYPE_KEY,
                  WAF_OPEN.WF_ACTIVITY_TARGET_DATE_KEY,
                  NVL(WAF_CLOSED.WF_ACTIVITY_COLLEAGUE_KEY,WAF_OPEN.WF_ACTIVITY_COLLEAGUE_KEY
                  ),
                  NVL(WAF_CLOSED.WF_ACTIVITY_ALOC_COLL_KEY,WAF_OPEN.WF_ACTIVITY_ALOC_COLL_KEY
                  ),
                  WAF_OPEN.WF_ACTIVITY_REF,
                  WAF_OPEN.WF_ACTIVITY_ALT1_REF,
                  WAF_OPEN.WF_ACTIVITY_ALT2_REF,
                  NVL(WAF_CLOSED.WF_ACTIVITY_COMMENTS_TX,WAF_OPEN.WF_ACTIVITY_COMMENTS_TX),
                  WAF_OPEN.WF_ACTIVITY_RSN1_TYPE_KEY,
                  WAF_OPEN.WF_ACTIVITY_RSN2_TYPE_KEY,
                  WAF_OPEN.WF_ACTIVITY_ALOC_TEAM_KEY,
                  WAF_OPEN.WF_DUE_DAYS_NR,
                  WAF_OPEN.WF_SLA_DAYS_NR,
                  NVL(WOB_TO_BAS.BUSINESS_ASSOCIATE_DIM_KEY,  -3),
                  NVL(WOB_TO_BTD.BUSINESS_TRANSACTION_DIM_KEY,-3),
                  NVL(WOB_TO_RID.RISK_DIM_KEY,                -3),
                  NVL(WAF_CLOSED.ROW_SRC_SYS_KEY,WAF_OPEN.ROW_SRC_SYS_KEY),
                  NVL(WAF_CLOSED.ROW_SRC_SYS_COUNTRY_KEY,WAF_OPEN.ROW_SRC_SYS_COUNTRY_KEY),
                  NVL(WAF_CLOSED.ROW_INSERT_DT,WAF_OPEN.ROW_INSERT_DT),
                  NVL(WAF_CLOSED.ROW_INSERT_USER_TX,WAF_OPEN.ROW_INSERT_USER_TX),
                  NVL(WAF_CLOSED.ROW_INSERT_LOAD_NR,WAF_OPEN.ROW_INSERT_LOAD_NR),
                  NVL(WAF_CLOSED.ROW_INSERT_BATCH_NR,WAF_OPEN.ROW_INSERT_BATCH_NR),
                  NVL(WAF_CLOSED.ROW_LATEST_UPDATE_DT,WAF_OPEN.ROW_LATEST_UPDATE_DT),
                  NVL(WAF_CLOSED.ROW_LATEST_UPDATE_USER_TX,WAF_OPEN.ROW_LATEST_UPDATE_USER_TX
                  ),
                  NVL(WAF_CLOSED.ROW_LATEST_UPDATE_BATCH_NR,
                  WAF_OPEN.ROW_LATEST_UPDATE_BATCH_NR),
                  NVL(WAF_CLOSED.ROW_LATEST_UPDATE_LOAD_NR,WAF_OPEN.ROW_LATEST_UPDATE_LOAD_NR
                  ),
                  NVL(WAF_CLOSED.ROW_SRC_SYS_DELETED_FL,WAF_OPEN.ROW_SRC_SYS_DELETED_FL)
                FROM
                  (
                    SELECT
                      WAF_OPEN_LATEST.*
                    FROM
                      (
                        SELECT
                          WORKFLOW_ACTIVITY_FACT_WAF.*,
                          RANK() OVER (PARTITION BY WF_ACTIVITY_ALT1_REF,
                          WF_ACTIVITY_STATUS_TYPE_KEY ORDER BY ROW_LATEST_UPDATE_DT DESC)
                          RNK_OPEN
                        FROM
                          WORKFLOW_ACTIVITY_FACT_WAF
                      )
                      WAF_OPEN_LATEST,
                      TYPE_DIM_TYP
                    WHERE
                      WAF_OPEN_LATEST.WF_ACTIVITY_STATUS_TYPE_KEY = TYPE_DIM_KEY
                    AND TYPE_CATEGORY_CODE_CD                     = 'WF_ACTIVITY_STATUS'
                    AND TYPE_CD                                   = 'O'
                    AND WAF_OPEN_LATEST.RNK_OPEN                  = 1
                  )
                  WAF_OPEN,
                  (
                    SELECT
                      WAF_CLOSED_LATEST.*
                    FROM
                      (
                        SELECT
                          WORKFLOW_ACTIVITY_FACT_WAF.*,
                          RANK() OVER (PARTITION BY WF_ACTIVITY_ALT1_REF,
                          WF_ACTIVITY_STATUS_TYPE_KEY ORDER BY ROW_LATEST_UPDATE_DT DESC)
                          RNK_CLOSED
                        FROM
                          WORKFLOW_ACTIVITY_FACT_WAF
                      )
                      WAF_CLOSED_LATEST,
                      TYPE_DIM_TYP
                    WHERE
                      WAF_CLOSED_LATEST.WF_ACTIVITY_STATUS_TYPE_KEY = TYPE_DIM_KEY
                    AND TYPE_CATEGORY_CODE_CD                       = 'WF_ACTIVITY_STATUS'
                    AND TYPE_CD                                     = 'C'
                    AND WAF_CLOSED_LATEST.RNK_CLOSED                = 1
                  )
                  WAF_CLOSED,
                  WORKFLOW_EVENT_DIM_WED WED ,
                  TYPE_DIM_TYP TYP,
                  (
                    SELECT
                      WOB1.WORKFLOW_KEY WORKFLOW_KEY,
                      NVL(WOB1.WORKFLOW_BRIDGE_SUBJECT_KEY,
                      inBTD.BUSINESS_TRANSACTION_DIM_KEY) BUSINESS_TRANSACTION_DIM_KEY
                    FROM
                      WORKFLOW_BRIDGE_WOB WOB1,
                      TYPE_DIM_TYP TYP1 ,
                      (
                        SELECT
                          BUSINESS_TRANSACTION_DIM_KEY
                        FROM
                          BUSINESS_TRANSACTION_DIM_BTD
                        WHERE
                          BUSINESS_TRANSACTION_NK = '-2'
                      )
                      inBTD
                    WHERE
                      TYP1.TYPE_CD                             = 'BUSINESS_TRANSACTION_DIM_BTD'
                    AND TYP1.TYPE_CATEGORY_CODE_CD             = 'WORKFLOW_BRIDGE_SUBJ'
                    AND WOB1.WORKFLOW_BRIDGE_SUBJ_TYPE_KEY = TYP1.TYPE_DIM_KEY
                  )
                  WOB_TO_BTD,
                  (
                    SELECT
                      WOB3.WORKFLOW_KEY WORKFLOW_KEY,
                      NVL(WOB3.WORKFLOW_BRIDGE_SUBJECT_KEY, inBAS.BUSINESS_ASSOCIATE_DIM_KEY)
                      BUSINESS_ASSOCIATE_DIM_KEY
                    FROM
                      WORKFLOW_BRIDGE_WOB WOB3,
                      TYPE_DIM_TYP TYP3 ,
                      (
                        SELECT
                          BUSINESS_ASSOCIATE_DIM_KEY
                        FROM
                          BUSINESS_ASSOCIATE_DIM_BAS
                        WHERE
                          BUSINESS_ASSOCIATE_NK = '-2'
                      )
                      inBAS
                    WHERE
                      TYP3.TYPE_CD                             = 'BUSINESS_ASSOCIATE_DIM_BAS'
                    AND TYP3.TYPE_CATEGORY_CODE_CD             = 'WORKFLOW_BRIDGE_SUBJ'
                    AND WOB3.WORKFLOW_BRIDGE_SUBJ_TYPE_KEY = TYP3.TYPE_DIM_KEY
                  )
                  WOB_TO_BAS,
                  (
                    SELECT
                      WOB2.WORKFLOW_KEY WORKFLOW_KEY,
                      NVL(WOB2.WORKFLOW_BRIDGE_SUBJECT_KEY, inRID.RISK_DIM_KEY) RISK_DIM_KEY
                    FROM
                      WORKFLOW_BRIDGE_WOB WOB2,
                      TYPE_DIM_TYP TYP2 ,
                      (
                        SELECT
                          RISK_DIM_KEY
                        FROM
                          RISK_DIM_RID
                        WHERE
                          RISK_NUMBER_NK = '-2'
                      )
                      inRID
                    WHERE
                      TYP2.TYPE_CD                             = 'RISK_DIM_RID'
                    AND TYP2.TYPE_CATEGORY_CODE_CD             = 'WORKFLOW_BRIDGE_SUBJ'
                    AND WOB2.WORKFLOW_BRIDGE_SUBJ_TYPE_KEY = TYP2.TYPE_DIM_KEY
                  )
                  WOB_TO_RID
                WHERE
                  WAF_OPEN.WF_ACTIVITY_ALT1_REF = WAF_CLOSED.WF_ACTIVITY_ALT1_REF
                AND WAF_OPEN.WF_EVENT_KEY       = WED.WORKFLOW_EVENT_DIM_KEY
                AND TYP.TYPE_CD                 = 'PLACEMENT'
                AND TYP.TYPE_CATEGORY_CODE_CD   = 'WORKFLOW_EVENT'
                AND WED.WORKFLOW_EVENT_TYPE_KEY = TYP.TYPE_DIM_KEY
                  /* WED Generate Business Associate Key */
                AND WOB_TO_BAS.WORKFLOW_KEY = WED.WORKFLOW_EVENT_DIM_KEY
                  /* WED Generate Business Transaction Key */
                AND WOB_TO_BTD.WORKFLOW_KEY = WED.WORKFLOW_EVENT_DIM_KEY
                  /* WED Generate RISK DIM Key */
                AND WOB_TO_RID.WORKFLOW_KEY = WED.WORKFLOW_EVENT_DIM_KEY;

               
              /* Underlying table structures */
              desc RISK_DIM_RID
              Name                           Null     Type          
              ------------------------------ -------- --------------
              RISK_DIM_KEY                   NOT NULL NUMBER(38)    
              BUSINESS_TRANSACTION_DIM_KEY            NUMBER(38)    
              RISK_NUMBER_NK                 NOT NULL VARCHAR2(100) 
              RISK_HEADER_NK                          VARCHAR2(100) 
              RISK_REF                                VARCHAR2(250) 
              RISK_VERSION_REF                        VARCHAR2(250) 
              BACK_OFFICE_REF                         VARCHAR2(250) 
              MARKET_QUOTE_REF                        VARCHAR2(250) 
              RISK_SHORT_NM                           VARCHAR2(250) 
              UNIQUE_MARKET_REF                       VARCHAR2(250) 
              RISK_CREATION_DATE_KEY                  NUMBER(38)    
              TIMEZONE_DIM_KEY                        NUMBER(38)    
              FIRST_INCEPTED_DATE_KEY                 NUMBER(38)    
              TERM_NR                                 NUMBER(38)    
              RISK_COST_CENTRE_HIER_KEY               NUMBER(38)    
              RISK_INCEPTION_DATE_KEY                 NUMBER(38)    
              RISK_INCEPTION_TIME_KEY                 NUMBER(38)    
              RISK_ANNIVERSARY_DATE_KEY               NUMBER(38)    
              RISK_EXPIRY_DATE_KEY                    NUMBER(38)    
              RISK_EXPIRY_TIME_KEY                    NUMBER(38)    
              LTA_EXPIRY_DATE_KEY                     NUMBER(38)    
              RISK_CANCELLED_DATE_KEY                 NUMBER(38)    
              RISK_RECURRING_IN                       VARCHAR2(10)  
              RISK_YEAR_OF_ACCOUNT_TX                 VARCHAR2(50)  
              RISK_RENEWAL_DATE_KEY                   NUMBER(38)    
              RISK_RENEWAL_REVIEW_DATE_KEY            NUMBER(38)    
              RISK_BOUND_DATE_KEY                     NUMBER(38)    
              RISK_FIRM_ORDER_DATE_KEY                NUMBER(38)    
              RISK_STATE_TYPE_KEY                     NUMBER(38)    
              RISK_RECORDED_STATE_TYPE_KEY            NUMBER(38)    
              PLACING_STATUS_TYPE_KEY                 NUMBER(38)    
              PLACING_STATUS_REASON_TYPE_KEY          NUMBER(38)    
              PLACING_STATUS_MESSAGE_TX               VARCHAR2(4000)
              RISK_INVOICE_RAISED_FL                  VARCHAR2(10)  
              QUOTE_RISK_KEY                          NUMBER(38)    
              ORIGINATING_CHANNEL_TYPE_KEY            NUMBER(38)    
              EVIDENCE_OF_COVER_TYPE_KEY              NUMBER(38)    
              RISK_CONTRACT_DESC                      VARCHAR2(4000)
              RISK_SUB_TYPE_KEY                       NUMBER(38)    
              RISK_CATEGORY_TYPE_KEY                  NUMBER(38)    
              RISK_BUSINESS_TYPE_KEY                  NUMBER(38)    
              RISK_POLICY_TYPE_KEY                    NUMBER(38)    
              RISK_LINESLIP_TYPE_KEY                  NUMBER(38)    
              RISK_PRODUCTION_SRCE_TYPE_KEY           NUMBER(38)    
              FIRST_AUTH_CLOSING_DATE_KEY             NUMBER(38)    
              RISK_REGULATED_FL                       VARCHAR2(10)  
              RISK_REGULATORY_CLASS_TYPE_KEY          NUMBER(38)    
              REVENUE_BASIS_TYPE_KEY                  NUMBER(38)    
              RISK_TRADING_TYPE_KEY                   NUMBER(38)    
              RISK_LANGUAGE_DIM_KEY                   NUMBER(38)    
              RISK_FORM_REF                           VARCHAR2(250) 
              PRODUCER_ENGAGEMENT_TYPE_KEY            NUMBER(38)    
              ROW_EFFECTIVE_START_DT         NOT NULL DATE          
              ROW_EFFECTIVE_END_DT                    DATE          
              ROW_SRC_SYS_KEY                NOT NULL NUMBER(38)    
              ROW_SRC_SYS_COUNTRY_KEY        NOT NULL NUMBER(38)    
              ROW_INSERT_DT                  NOT NULL DATE          
              ROW_INSERT_LOAD_NR             NOT NULL NUMBER(38)    
              ROW_INSERT_USER_TX             NOT NULL VARCHAR2(50)  
              ROW_INSERT_BATCH_NR            NOT NULL NUMBER(38)    
              ROW_LATEST_UPDATE_DT           NOT NULL DATE          
              ROW_LATEST_UPDATE_USER_TX      NOT NULL VARCHAR2(50)  
              ROW_LATEST_UPDATE_BATCH_NR     NOT NULL NUMBER(38)    
              ROW_LATEST_UPDATE_LOAD_NR      NOT NULL NUMBER(38)    
              ROW_SRC_SYS_DELETED_FL         NOT NULL VARCHAR2(10)  

              desc WORKFLOW_BRIDGE_WOB
              Name                          Null     Type         
              ----------------------------- -------- -------------
              WORKFLOW_BRIDGE_KEY           NOT NULL NUMBER(38)   
              WORKFLOW_BRIDGE_NK            NOT NULL VARCHAR2(150)
              WORKFLOW_BRIDGE_TYPE_KEY      NOT NULL NUMBER(38)   
              WORKFLOW_KEY                  NOT NULL NUMBER(38)   
              WORKFLOW_BRIDGE_SUBJ_TYPE_KEY NOT NULL NUMBER(38)   
              ROW_SRC_SYS_KEY               NOT NULL NUMBER(38)   
              ROW_SRC_SYS_COUNTRY_KEY       NOT NULL NUMBER(38)   
              ROW_INSERT_DT                 NOT NULL DATE         
              ROW_INSERT_LOAD_NR                     NUMBER(38)   
              ROW_INSERT_BATCH_NR                    NUMBER(38)   
              ROW_INSERT_USER_TX            NOT NULL VARCHAR2(50) 
              ROW_LATEST_UPDATE_BATCH_NR    NOT NULL NUMBER(38)   
              ROW_LATEST_UPDATE_DT          NOT NULL DATE         
              ROW_LATEST_UPDATE_LOAD_NR     NOT NULL NUMBER(38)   
              ROW_LATEST_UPDATE_USER_TX     NOT NULL VARCHAR2(50) 
              ROW_SRC_SYS_DELETED_FL        NOT NULL VARCHAR2(10) 
              WORKFLOW_BRIDGE_SUBJECT_KEY            NUMBER(38)   

              desc TYPE_DIM_TYP
              Name                        Null     Type          
              --------------------------- -------- --------------
              TYPE_DIM_KEY                NOT NULL NUMBER(38)    
              TYPE_CODE_NK                NOT NULL VARCHAR2(100) 
              TYPE_CD                              VARCHAR2(400) 
              TYPE_NAME_TX                         VARCHAR2(500) 
              TYPE_DESC                            VARCHAR2(4000)
              TYPE_CATEGORY_CODE_CD                VARCHAR2(100) 
              SUPERTYPE_TYPE_CODE_CD               VARCHAR2(50)  
              GLOBAL_CD                            VARCHAR2(50)  
              GLOBAL_CATEGORY_CD                   VARCHAR2(50)  
              GLOBAL_NAME_TX                       VARCHAR2(250) 
              SUPERTYPE_DESC                       VARCHAR2(500) 
              ULT_TYPE_CODE_CD                     VARCHAR2(50)  
              ULT_TYPE_DESC                        VARCHAR2(4000)
              ROW_EFFECTIVE_START_DT      NOT NULL DATE          
              ROW_EFFECTIVE_END_DT                 DATE          
              ROW_SRC_SYS_KEY             NOT NULL NUMBER(38)    
              ROW_SRC_SYS_COUNTRY_KEY     NOT NULL NUMBER(38)    
              ROW_INSERT_DT               NOT NULL DATE          
              ROW_INSERT_USER_TX          NOT NULL VARCHAR2(50)  
              ROW_INSERT_BATCH_NR         NOT NULL NUMBER(38)    
              ROW_INSERT_LOAD_NR          NOT NULL NUMBER(38)    
              ROW_LATEST_UPDATE_DT        NOT NULL DATE          
              ROW_LATEST_UPDATE_USER_TX   NOT NULL VARCHAR2(50)  
              ROW_LATEST_UPDATE_BATCH_NR  NOT NULL NUMBER(38)    
              ROW_LATEST_UPDATE_LOAD_NR   NOT NULL NUMBER(38)    
              ROW_SRC_SYS_DELETED_FL      NOT NULL VARCHAR2(10)  
              TYPE_ALT1_HIERARCHY_CODE_CD          VARCHAR2(100) 
              TYPE_ALT1_HIERARCHY_DESC             VARCHAR2(4000)
              TYPE_ALT2_HIERARCHY_CODE_CD          VARCHAR2(100) 
              TYPE_ALT2_HIERARCHY_DESC             VARCHAR2(4000)
              TYPE_STATUS_FL              NOT NULL VARCHAR2(10)  

              desc BUSINESS_ASSOCIATE_DIM_BAS
              Name                           Null     Type          
              ------------------------------ -------- --------------
              BUSINESS_ASSOCIATE_DIM_KEY     NOT NULL NUMBER(38)    
              BUSINESS_ASSOC_PARENT_KEY               NUMBER(38)    
              BUSINESS_ASSOC_INCORP_CNTY_KEY          NUMBER(38)    
              BUSINESS_ASSOC_TYPE_KEY                 NUMBER(38)    
              BUSINESS_ASSOC_STATUS_TYPE_KEY          NUMBER(38)    
              BUSINESS_ASSOCIATE_NK          NOT NULL VARCHAR2(100) 
              BUSINESS_ASSOC_SHORT_NAME_TX            VARCHAR2(255) 
              BUSINESS_ASSOC_NAME_TX                  VARCHAR2(4000)
              BUSINESS_ASSOC_LEGAL_NAME_TX            VARCHAR2(250) 
              BUSINESS_ASSOC_PARENT_NK       NOT NULL VARCHAR2(100) 
              BUSINESS_ASSOCIATE_TEL_NR               VARCHAR2(50)  
              BUSINESS_ASSOCIATE_EMAIL_ADD            VARCHAR2(150) 
              BUSINESS_ASSOCIATE_REF         NOT NULL VARCHAR2(150) 
              BUSINESS_ASSOC_GROUP_TYPE_KEY           NUMBER(38)    
              BUS_ASSOC_SECURITY_TYPE_KEY             NUMBER(38)    
              BUS_ASSOC_SECURITY_TX                   VARCHAR2(250) 
              LEGAL_STATUS_TX                         VARCHAR2(50)  
              LEGAL_STATUS_DATE_KEY          NOT NULL NUMBER(38)    
              ROW_EFFECTIVE_START_DT         NOT NULL DATE          
              ROW_EFFECTIVE_END_DT                    DATE          
              ROW_SRC_SYS_KEY                NOT NULL NUMBER(38)    
              ROW_SRC_SYS_COUNTRY_KEY        NOT NULL NUMBER(38)    
              ROW_INSERT_DT                  NOT NULL DATE          
              ROW_INSERT_USER_TX             NOT NULL VARCHAR2(50)  
              ROW_INSERT_BATCH_NR                     NUMBER(38)    
              ROW_INSERT_LOAD_NR             NOT NULL NUMBER(38)    
              ROW_LATEST_UPDATE_DT           NOT NULL DATE          
              ROW_LATEST_UPDATE_USER_TX      NOT NULL VARCHAR2(50)  
              ROW_LATEST_UPDATE_BATCH_NR     NOT NULL NUMBER(38)    
              ROW_LATEST_UPDATE_LOAD_NR      NOT NULL NUMBER(38)    
              ROW_SRC_SYS_DELETED_FL         NOT NULL VARCHAR2(10)  
              BUSINESS_ASSOCIATE_ALT1_REF             VARCHAR2(150) 
              BUSINESS_ASSOCIATE_ALT2_REF             VARCHAR2(150) 
              BUSINESS_ASSOCIATE_ALT3_REF             VARCHAR2(150) 

              desc BUSINESS_TRANSACTION_DIM_BTD
              Name                           Null     Type          
              ------------------------------ -------- --------------
              BUSINESS_TRANSACTION_DIM_KEY   NOT NULL NUMBER(38)    
              BUSINESS_TRANSACTION_NK        NOT NULL VARCHAR2(100) 
              BUSINESS_TRANS_DATE_KEY        NOT NULL NUMBER(38)    
              TIMEZONE_DIM_KEY                        NUMBER(38)    
              BUSINESS_TRANS_EFF_DATE_KEY             NUMBER(38)    
              BUSINESS_TRANS_EFF_TIME_KEY             NUMBER(38)    
              BUSINESS_TRANS_EXP_DATE_KEY             NUMBER(38)    
              BUSINESS_TRANS_EXP_TIME_KEY             NUMBER(38)    
              BUSINESS_TRANS_REF                      VARCHAR2(150) 
              BUSINESS_TRANS_TYPE_KEY                 NUMBER(38)    
              BUSINESS_TRANS_SUB_TYPE_KEY             NUMBER(38)    
              BUSINESS_TRANS_STATUS_TYPE_KEY          NUMBER(38)    
              BUS_TRANS_SUB_STATUS_TYPE_KEY           NUMBER(38)    
              BUS_TRANS_REASON_TYPE_KEY               NUMBER(38)    
              BUSINESS_TRANS_NARRATIVE1_TX            VARCHAR2(4000)
              BUSINESS_TRANS_NARRATIVE2_TX            VARCHAR2(4000)
              BUSINESS_TRANS_DESC                     VARCHAR2(4000)
              ROW_EFFECTIVE_START_DT                  DATE          
              ROW_EFFECTIVE_END_DT                    DATE          
              ROW_SRC_SYS_KEY                NOT NULL NUMBER(38)    
              ROW_SRC_SYS_COUNTRY_KEY        NOT NULL NUMBER(38)    
              ROW_INSERT_DT                  NOT NULL DATE          
              ROW_INSERT_LOAD_NR             NOT NULL NUMBER(38)    
              ROW_INSERT_USER_TX             NOT NULL VARCHAR2(50)  
              ROW_INSERT_BATCH_NR            NOT NULL NUMBER(38)    
              ROW_LATEST_UPDATE_DT           NOT NULL DATE          
              ROW_LATEST_UPDATE_USER_TX      NOT NULL VARCHAR2(50)  
              ROW_LATEST_UPDATE_BATCH_NR     NOT NULL NUMBER(38)    
              ROW_LATEST_UPDATE_LOAD_NR      NOT NULL NUMBER(38)    
              ROW_SRC_SYS_DELETED_FL         NOT NULL VARCHAR2(10)  

              desc WORKFLOW_EVENT_DIM_WED
              Name                           Null     Type          
              ------------------------------ -------- --------------
              WORKFLOW_EVENT_DIM_KEY         NOT NULL NUMBER(38)    
              WORKFLOW_EVENT_DATE_KEY        NOT NULL NUMBER(38)    
              WORKFLOW_EVENT_TIME_KEY        NOT NULL NUMBER(38)    
              WORKFLOW_SYSTEM_REF_NK                  VARCHAR2(50)  
              WORKFLOW_EVENT_REF1                     VARCHAR2(250) 
              WORKFLOW_EVENT_REF2                     VARCHAR2(250) 
              WORKFLOW_EVENT_TYPE_KEY                 NUMBER(38)    
              WORKFLOW_EVENT_TYPE_STATUS_KEY          NUMBER(38)    
              WORKFLOW_EVENT_COMMENTS_TX              VARCHAR2(4000)
              WORKFLOW_EVENT_SHORT_NM                 VARCHAR2(100) 
              WF_EVENT_OUTCOME_COMMENTS_TX            VARCHAR2(4000)
              WF_EVENT_FAILURE_CONSEQ_TX              VARCHAR2(4000)
              WF_COMPLETION_TYPE_KEY                  NUMBER(38)    
              WORKFLOW_EVENT_OWNING_TEAM_KEY          NUMBER(38)    
              WORKFLOW_EVENT_OWNING_COLL_KEY          NUMBER(38)    
              DURATION_FOR_UNIT_OF_WORK_TX            VARCHAR2(250) 
              MEASURE_OF_UNIT_OF_WORK_TX              VARCHAR2(250) 
              ASSOCIATED_MEASURES_OF_WORK_TX          VARCHAR2(250) 
              ROW_SRC_SYS_KEY                NOT NULL NUMBER(38)    
              ROW_SRC_SYS_COUNTRY_KEY        NOT NULL NUMBER(38)    
              ROW_INSERT_DT                  NOT NULL DATE          
              ROW_INSERT_USER_TX             NOT NULL VARCHAR2(50)  
              ROW_INSERT_BATCH_NR            NOT NULL NUMBER(38)    
              ROW_INSERT_LOAD_NR             NOT NULL NUMBER(38)    
              ROW_LATEST_UPDATE_DT           NOT NULL DATE          
              ROW_LATEST_UPDATE_USER_TX      NOT NULL VARCHAR2(50)  
              ROW_LATEST_UPDATE_BATCH_NR     NOT NULL NUMBER(38)    
              ROW_LATEST_UPDATE_LOAD_NR      NOT NULL NUMBER(38)    
              ROW_SRC_SYS_DELETED_FL         NOT NULL VARCHAR2(10)  

              desc WORKFLOW_ACTIVITY_FACT_WAF
              Name                          Null     Type          
              ----------------------------- -------- --------------
              WF_ACTIVITY_FACT_KEY          NOT NULL NUMBER(38)    
              WF_ACTIVITY_NK                NOT NULL VARCHAR2(100) 
              WF_EVENT_KEY                  NOT NULL NUMBER(38)    
              WF_ACTIVITY_BUS_ASSOC_KEY     NOT NULL NUMBER(38)    
              WF_ACTIVITY_BA_ROLE_TYPE_KEY  NOT NULL NUMBER(38)    
              WF_ACTIVITY_STATUS_TYPE_KEY   NOT NULL NUMBER(38)    
              WF_ACTIVITY_DATE_KEY          NOT NULL NUMBER(38)    
              WF_ACTIVITY_TIME_KEY          NOT NULL NUMBER(38)    
              WF_ACTIVITY_TYPE_KEY          NOT NULL NUMBER(38)    
              WF_ACTIVITY_CLASS_TYPE_KEY             NUMBER(38)    
              WF_ACT_TARGET_STATUS_TYPE_KEY NOT NULL NUMBER(38)    
              WF_ACTIVITY_TARGET_DATE_KEY   NOT NULL NUMBER(38)    
              WF_ACTIVITY_COLLEAGUE_KEY     NOT NULL NUMBER(38)    
              WF_ACTIVITY_ALOC_COLL_KEY     NOT NULL NUMBER(38)    
              WF_ACTIVITY_REF                        VARCHAR2(250) 
              WF_ACTIVITY_ALT1_REF                   VARCHAR2(250) 
              WF_ACTIVITY_ALT2_REF                   VARCHAR2(250) 
              WF_ACTIVITY_COMMENTS_TX                VARCHAR2(4000)
              WF_ACTIVITY_RSN1_TYPE_KEY     NOT NULL NUMBER(38)    
              WF_ACTIVITY_RSN2_TYPE_KEY     NOT NULL NUMBER(38)    
              WF_ACTIVITY_ALOC_TEAM_KEY     NOT NULL NUMBER(38)    
              WF_DUE_DAYS_NR                         NUMBER(10)    
              WF_SLA_DAYS_NR                         NUMBER(10)    
              ROW_SRC_SYS_KEY               NOT NULL NUMBER(38)    
              ROW_SRC_SYS_COUNTRY_KEY       NOT NULL NUMBER(38)    
              ROW_INSERT_DT                 NOT NULL DATE          
              ROW_INSERT_USER_TX            NOT NULL VARCHAR2(50)  
              ROW_INSERT_LOAD_NR            NOT NULL NUMBER(38)    
              ROW_INSERT_BATCH_NR           NOT NULL NUMBER(38)    
              ROW_LATEST_UPDATE_DT          NOT NULL DATE          
              ROW_LATEST_UPDATE_USER_TX              VARCHAR2(50)  
              ROW_LATEST_UPDATE_BATCH_NR    NOT NULL NUMBER(38)    
              ROW_LATEST_UPDATE_LOAD_NR     NOT NULL NUMBER(38)    
              ROW_SRC_SYS_DELETED_FL        NOT NULL VARCHAR2(10)  
              WF_ACTIVITY_AM                         NUMBER(38,6)  
              WF_ACTIVITY_RSN3_TYPE_KEY              NUMBER(38)    

              • 4. Re: ORA-01841 error while refreshing Materialized views
                user1758538

                there are no triggers on the mv and yes it is a pre-built mv. See the DDL for the MV in the reply to BluShadow

                • 5. Re: ORA-01841 error while refreshing Materialized views
                  user1758538

                  just for more information, when create a normal (non materialized) view in the database using the select statement as given for the mview above, it works without any errors.

                  • 6. Re: ORA-01841 error while refreshing Materialized views
                    BluShadow

                    user1758538 wrote:

                     

                    just for more information, when create a normal (non materialized) view in the database using the select statement as given for the mview above, it works without any errors.

                     

                    There's a big difference between the two.

                    A normal view is just a stored select statement, so no data is queried until you actually select from the view.

                    A materialized view, stores the select statement but also queries the data to materialize it into a table.

                    • 7. Re: ORA-01841 error while refreshing Materialized views
                      user1758538

                      sorry I should have mentioned, I have queried the normal view and seen all the rows that it could return (nearly 14K). So my suspicion is it's not the select that is causing grief here. I don't know much but could it be any of the oracle internal tables/parameters supporting mviews that could be causing this issue?

                      • 8. Re: ORA-01841 error while refreshing Materialized views
                        BluShadow

                        Well, if you have got dates stored in VARCHAR2 columns and they are trying to get converted to DATE datatypes implicitly, then it will take your session's date format parameter to do that conversion.  When you query the data yourself, your session parameter is likely ok to deal with it, but when the materialized view tries to refresh, that's not using your session's date format parameter as it's internal, and is likely a different format by default.... hence the conversion fails on some data.

                         

                        I can't easily follow all your MV query due to the way it's formatted and written (using * in place of column names is bad design, and doesn't help anyone, and is likely to break code in the future)

                        It's also not clear what each of the columns on the tables relate to.

                         

                        Are all of those "tables" actually tables, or are there any that are views, or materialized views themselves?

                        • 9. Re: ORA-01841 error while refreshing Materialized views
                          user1758538

                          On further step by step elimination I discovered that probably Oracle is trying to interpret a NUMBER column as a DATE column. Not sure why.

                           

                          For example the following code fails:

                           

                          CREATE MATERIALIZED VIEW TYPE_MV

                          AS

                            SELECT

                              TYPE_DIM_KEY,

                              TYPE_CODE_NK,

                              TYPE_CD,

                              TYPE_NAME_TX,

                              TYPE_DESC,

                              TYPE_CATEGORY_CODE_CD,

                              SUPERTYPE_TYPE_CODE_CD,

                              GLOBAL_CD,

                              GLOBAL_CATEGORY_CD,

                              GLOBAL_NAME_TX,

                              SUPERTYPE_DESC,

                              ULT_TYPE_CODE_CD,

                              ULT_TYPE_DESC,

                              ROW_EFFECTIVE_START_DT,

                              ROW_EFFECTIVE_END_DT,

                              ROW_SRC_SYS_KEY,

                              ROW_SRC_SYS_COUNTRY_KEY,

                              ROW_INSERT_DT,

                              ROW_INSERT_USER_TX,

                              ROW_INSERT_BATCH_NR,

                              ROW_INSERT_LOAD_NR,

                              ROW_LATEST_UPDATE_DT,

                              ROW_LATEST_UPDATE_USER_TX,

                              ROW_LATEST_UPDATE_BATCH_NR,

                              ROW_LATEST_UPDATE_LOAD_NR,

                              ROW_SRC_SYS_DELETED_FL,

                              TYPE_ALT1_HIERARCHY_CODE_CD,

                              TYPE_ALT1_HIERARCHY_DESC,

                              TYPE_ALT2_HIERARCHY_CODE_CD,

                              TYPE_ALT2_HIERARCHY_DESC,

                              TYPE_STATUS_FL

                            FROM

                              TYPE_DIM_TYP

                          ;

                          Error at Command Line : 36 Column : 5

                          Error report -

                          SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0

                          01841. 00000 -  "(full) year must be between -4713 and +9999, and not be 0"

                          *Cause:    Illegal year entered

                          *Action:   Input year in the specified range

                           

                          If I comment the first column TYPE_DIM_KEY, which happens to be the Primary Key of the underlying table and is defined as a number, the MVIEW creation succeeds

                           

                          CREATE MATERIALIZED VIEW TYPE_MV

                          AS

                            SELECT

                            /*  TYPE_DIM_KEY, */

                              TYPE_CODE_NK,

                              TYPE_CD,

                              TYPE_NAME_TX,

                              TYPE_DESC,

                              TYPE_CATEGORY_CODE_CD,

                              SUPERTYPE_TYPE_CODE_CD,

                              GLOBAL_CD,

                              GLOBAL_CATEGORY_CD,

                              GLOBAL_NAME_TX,

                              SUPERTYPE_DESC,

                              ULT_TYPE_CODE_CD,

                              ULT_TYPE_DESC,

                              ROW_EFFECTIVE_START_DT,

                              ROW_EFFECTIVE_END_DT,

                              ROW_SRC_SYS_KEY,

                              ROW_SRC_SYS_COUNTRY_KEY,

                              ROW_INSERT_DT,

                              ROW_INSERT_USER_TX,

                              ROW_INSERT_BATCH_NR,

                              ROW_INSERT_LOAD_NR,

                              ROW_LATEST_UPDATE_DT,

                              ROW_LATEST_UPDATE_USER_TX,

                              ROW_LATEST_UPDATE_BATCH_NR,

                              ROW_LATEST_UPDATE_LOAD_NR,

                              ROW_SRC_SYS_DELETED_FL,

                              TYPE_ALT1_HIERARCHY_CODE_CD,

                              TYPE_ALT1_HIERARCHY_DESC,

                              TYPE_ALT2_HIERARCHY_CODE_CD,

                              TYPE_ALT2_HIERARCHY_DESC,

                              TYPE_STATUS_FL

                            FROM

                              TYPE_DIM_TYP

                          ;

                          materialized view TYPE_MV created.

                           

                          DESC TYPE_DIM_TYP

                          Name                        Null     Type          
                          --------------------------- -------- --------------
                          TYPE_DIM_KEY                NOT NULL NUMBER(38)    
                          TYPE_CODE_NK                NOT NULL VARCHAR2(100) 
                          TYPE_CD                              VARCHAR2(400) 
                          TYPE_NAME_TX                         VARCHAR2(500) 
                          TYPE_DESC                            VARCHAR2(4000)
                          TYPE_CATEGORY_CODE_CD                VARCHAR2(100) 
                          SUPERTYPE_TYPE_CODE_CD               VARCHAR2(50)  
                          GLOBAL_CD                            VARCHAR2(50)  
                          GLOBAL_CATEGORY_CD                   VARCHAR2(50)  
                          GLOBAL_NAME_TX                       VARCHAR2(250) 
                          SUPERTYPE_DESC                       VARCHAR2(500) 
                          ULT_TYPE_CODE_CD                     VARCHAR2(50)  
                          ULT_TYPE_DESC                        VARCHAR2(4000)
                          ROW_EFFECTIVE_START_DT      NOT NULL DATE          
                          ROW_EFFECTIVE_END_DT                 DATE          
                          ROW_SRC_SYS_KEY             NOT NULL NUMBER(38)    
                          ROW_SRC_SYS_COUNTRY_KEY     NOT NULL NUMBER(38)    
                          ROW_INSERT_DT               NOT NULL DATE          
                          ROW_INSERT_USER_TX          NOT NULL VARCHAR2(50)  
                          ROW_INSERT_BATCH_NR         NOT NULL NUMBER(38)    
                          ROW_INSERT_LOAD_NR          NOT NULL NUMBER(38)    
                          ROW_LATEST_UPDATE_DT        NOT NULL DATE          
                          ROW_LATEST_UPDATE_USER_TX   NOT NULL VARCHAR2(50)  
                          ROW_LATEST_UPDATE_BATCH_NR  NOT NULL NUMBER(38)    
                          ROW_LATEST_UPDATE_LOAD_NR   NOT NULL NUMBER(38)    
                          ROW_SRC_SYS_DELETED_FL      NOT NULL VARCHAR2(10)  
                          TYPE_ALT1_HIERARCHY_CODE_CD          VARCHAR2(100) 
                          TYPE_ALT1_HIERARCHY_DESC             VARCHAR2(4000)
                          TYPE_ALT2_HIERARCHY_CODE_CD          VARCHAR2(100) 
                          TYPE_ALT2_HIERARCHY_DESC             VARCHAR2(4000)
                          TYPE_STATUS_FL              NOT NULL VARCHAR2(10) 

                           

                           

                          Any thoughts why Oracle is trying to apply Date validations on a NUMBER column?

                           

                          I hope this provide enough info. Let me know if any further info is needed.

                          • 10. Re: ORA-01841 error while refreshing Materialized views
                            BluShadow

                            What tool are you running the statement through?

                            It doesn't look like SQL*Plus, and I suspect it would work ok in SQL*Plus.

                            I'm guessing your tool is doing something it shouldn't.