Does a materialize view refresh in its creation?

Question

I create a materialized view with the following cast function. It does not give me an error during creation although it exists an error. But refresh gives the error.

CREATE MATERIALIZED VIEW XXXX_ROW_MV
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
AS
SELECT
    CAST ('null' AS NUMBER)   currency_rate,
     CAST ('null' AS NUMBER)   conversion_factor;

Does this mean that materialized views compile until it is being refreshed?

refresh can be done by using following code

begin
  DBMS_MVIEW.refresh('XXXX_ROW_MV');

end;

Answer 1

The BUILD DEFERRED clause in the CREATE MATERIALIZED VIEW DDL statement causes the actual population to be deferred until the first refresh.

While Oracle parses the actual SQL used to populate the view, it does not execute it & will therefore not pick up "runtime" problems.

This is easily demonstrated.

Parsing error, due to NONSENSE not being a datatype:

SQL> CREATE MATERIALIZED VIEW PARSINGERROR
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
AS
SELECT
    CAST ('null' AS NONSENSE) as parsing_error
     from dual; 


    CAST ('null' AS NONSENSE) as parsing_error
                    *
ERROR at line 6:
ORA-00902: invalid datatype

SQL>

Runtime error, that won't get picked up as it doesn't analyse the actual values involved, and is unaware that the included string isn't a number:

SQL> CREATE MATERIALIZED VIEW RUNTIMEERROR
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
AS
SELECT
    CAST ('notanumber' AS number) as runtime_error
     from dual;  

Materialized view created.

SQL>

SQL> begin
  2  DBMS_MVIEW.refresh('RUNTIMEERROR');
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01722: invalid number
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2566
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2779
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2748
ORA-06512: at line 2


SQL>