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>