Ora-01858 Received When Refreshing Materialized View (Doc ID 1340292.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.1 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

Running the following fast refresh gives:

execute dbms_mview.refresh('XXNHS_W_X_DTR_F_MV','F');

ORA-12008: error in materialized view refresh path
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 1

Changes

The master table has the following column defined:

CREATE TABLE BAW.W_X_DTR_F
(
LEDGER_WID NUMBER(15),
ACCOUNT_SEG1_ATTRIB VARCHAR2(50 CHAR),
...
W_UPDATE_DT DATE,
X_CUSTOM VARCHAR2(10 CHAR),
V_TRANSACTION_DT_WID DATE GENERATED ALWAYS AS (TO_DATE(TO_CHAR("TRANSACTION_DATE_WID"),'yyyymmdd')),
CURRENCY_CODE VARCHAR2(30 BYTE),
...
TABLESPACE APPSDATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
PARTITION BY RANGE (V_TRANSACTION_DT_WID)
...
;

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