Hi Tom,

We have one view with DDL

CREATE OR REPLACE FORCE VIEW FX_RATE
AS
SELECT
QUOTED_CURR
,TO_DATE(UPDATE_TIME,'DD/MM/RR') AS UPDATE_TIME
,PRICE
FROM RATES;

RATES DDL is:
CREATE TABLE RATES
(
QUOTED_CURR VARCHAR2(3 CHAR),
UPDATE_TIME DATE,
PRICE NUMBER(20,10)
);

There is a materialize view created on this view in another DB over DB Link.

CREATE MATERIALIZE VIEW MV_FX_RATE (QUOTED_CURR, UPDATE_TIME,PRICE)
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS
SELECT QUOTED_CURR,UPDATE_TIME,PRICE FROM FX_RATE@DB_LINK;

While doing periodic refresh, refresh is failing with error ORA-01858: a non-numeric character was found where a numeric was expected.

I suspect it is because of some date issue but same DDL is used in another lower environment and there it is working fine. Not sure what could be the reason.
Difference I found between two environments:

Not working environment:
MV table is created as
CREATE TABLE MV_FX_RATE (
QUOTED_CURR VARCHAR2(3 CHAR),
UPDATE_TIME DATE,
PRICE NUMBER(20,10)
);

Some data exist in this environment may be from initial creation and date part is showing timestamp as well.

Working environment:
MV table is created as
CREATE TABLE MV_FX_RATE (
QUOTED_CURR VARCHAR2(3 CHAR),
UPDATE_TIME DATE(8),
PRICE NUMBER(20,10)
);

Date part is showing only date and no timestamp as formated.

Is there data issues? But then shouldn't SELECT * from FX_RATE also fail when queried on source DB? It is working fine.
Should TO_DATE be changed to TRUNC?

Thanks,
Mangesh

alter session set nls_date_format = 'dd/mm/rr';

with rws as (
  select sysdate d from dual
)
  select to_date(d, 'dd/mm/rr') from rws;

TO_DATE(D,'DD/MM/RR')  
14/09/16 
   
alter session set nls_date_format = 'DD-MON-YYYY hh24:mi:ss';

with rws as (
  select sysdate d from dual
)
  select to_date(d, 'dd/mm/rr') from rws;

SQL Error: ORA-01858: a non-numeric character was found where a numeric was expected