1

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;
1
  • Try selecting from the MV you just "created". if it has data, it did.
    – Raj
    Jun 15, 2017 at 11:57

1 Answer 1

3

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>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.