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 !
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