DBMS Job scheduler for materialized view

Question

I have been trying to schedule the refresh of three materialized views simultaneously every night. I have used the below code

BEGIN
DBMS_SCHEDULER.CREATE_JOB 
(
JOB_NAME            => 'REFRESH_MVIEW',
JOB_TYPE            => 'PLSQL_BLOCK',
JOB_ACTION          => 'BEGIN DBMS_MVIEW.REFRESH("m_view1, m_view2, m_view3",''C''); END;',
NUMBER_OF_ARGUMENTS => 0,
START_DATE          => SYSTIMESTAMP,
REPEAT_INTERVAL     => 'FREQ=DAILY; BYHOUR=0',
END_DATE            => NULL,
ENABLED             => TRUE,
AUTO_DROP           => FALSE,
COMMENTS            => 'JOB TO REFRESH'
);
END;

But I am getting the following error after the job has been run

ORA-12012: error on auto execute of job 57179 ORA-06550: line ORA-06550: line 1, column 495: PLS-00114: identifier 'm_view1, m_view2' too long , column :

I understand that there is a constraint of 30 chars in the procedure name. So, does that mean I have to split the job into 3 different jobs? Could you please let me know where I am going wrong?

Thanks in Advance !

Answer 1

Could you please let me know where I am going wrong?

Try to write your statement like that instead:

BEGIN DBMS_MVIEW.REFRESH(''m_view1,m_view2,m_view3'',''C'')

Or

BEGIN DBMS_MVIEW.REFRESH(''m_view1,m_view2,m_view3'',''CCC'')

View list is specified using a string -- so using single quotes. I don't know if spaces are relevant in the view list. In doubt, I've removed them too.

Please note in the first case you have a default refresh for later two views. If you need a complete refresh for all your views, you have to specify CCC as the refresh method.

See https://docs.oracle.com/cd/A97630_01/server.920/a96568/rarmviea.htm#94135