Condition for Next Clause While Alter Materialized View

Question

I am trying to refresh a materialized view for some days and between some hours and my code:

ALTER MATERIALIZED VIEW M_MVIEW
REFRESH FORCE ON DEMAND START WITH TO_DATE('11-07-2014 10:30:00', 'DD-MM-YYYY HH24:MI:SS') 
                        NEXT (CASE WHEN (TO_CHAR(SYSDATE,'D','NLS_SORT = XTURKISH') = 5) AND (SYSDATE BETWEEN (TRUNC(TO_DATE('10-07-2014 21:00:00', 'DD-MM-YYYY HH24:MI:SS')) + 20/24) AND (TRUNC(TO_DATE('10-07-2014 21:00:00', 'DD-MM-YYYY HH24:MI:SS')) + 31/24)) THEN TRUNC(NEXT_DAY(TO_DATE('10-07-2014 21:00:00', 'DD-MM-YYYY HH24:MI:SS'), 'PAZARTESİ')) + 7/24
                                   WHEN (SYSDATE BETWEEN (TRUNC(TO_DATE('10-07-2014 21:00:00', 'DD-MM-YYYY HH24:MI:SS')) + 20/24) AND (TRUNC(TO_DATE('10-07-2014 21:00:00', 'DD-MM-YYYY HH24:MI:SS')) + 31/24)) THEN TRUNC(SYSDATE) + 31/24
                                   ELSE SYSDATE + 2/24 END)

But I am getting error; ORA-06502: PL/SQL: numeric or value error%s I tried to solve my problem but I couldn't. Does anyone have any idea for my problem?

Thanks.

Answer 1

For NEXT clause in a materialized view you can write CASE statement but it acceptes only two condition (it should be very simple like case when x=x then aa else bb end). My suggestion is that simplify your condition if you can.

Answer 2

The real error message is somewhat more telling:

ORA-12899: Value too large for column "SYS"."JOB$"."INTERVAL#" (actual: 595, maximum: 200)

so it seems you're trying to set a value for INTERVAL that is too large.

Personally, I never use the START WITH / NEXT for Materialized Views. I prefer creating a DBMS_SCHEDULER job for the refresh because

• the syntax for specifying the run dates is much, much better
• you get a history of your refreshes from USER_SCHEDULER_JOB_LOG
• you can enable/disable the refresh at will