My Oracle Support Banner

Mview Complete Refresh Fails With Ora-904 When NVL(TO_CHAR(MAX Functions are in the Select Statement (Doc ID 2728840.1)

Last updated on APRIL 17, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 19.8.0.0.0 and later
Information in this document applies to any platform.

Symptoms

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

 

On : 19.8.0.0.0 DB version


ORA-00904: "A5"."NVL(TO_CHAR(MAX( XXX . YYYYYY": invalid identifier


After  upgrading the database from 12.1.0.2 to 19.8, the following is encountered while refreshing Materialized View.

BEGIN dbms_mview.refresh('<<<mview name>>>>','C'); END;

*
ERROR at line 1:
ORA-12008: error in materialized view or zonemap refresh path
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3020
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2432
ORA-00904: "A5"."NVL(TO_CHAR(MAX( <<<date column>>>": invalid identifier   >>>>>there is NO A5 in the mview DDL
ORA-02063: preceding line from <<<<<dblink>>>>
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 88
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 253
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2413
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2976
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3263
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3295
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 16
ORA-06512: at line 1



-- select inside the mview works fine and returns rows.
-- only while refreshing the mview the  error ORA-904 is returned.
-- No virtual columns.
--No extended statistics

--This is complete refresh.

 

This is the line causing the problem:

NVL(TO_CHAR(MAX(<<<date column>>>),'YYYY-MM-DD HH24:MI:SS'),'0') mynvl

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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

In this Document
Symptoms
Changes
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.