ORA-27452: “BEGIN DBMS_MVIEW.REFRESH(“MVREMOTE”);END;” is an invalid name for adatabase object.
Cause:
When creating a scheduler job for mview refresh, Job type cannot be used as Stored procedure for dbms_mview.refresh because it is a pl/sql block which calls the stored procedure.
kIsH@xHydra<>BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'MV_MVREMOTE_REFRESH1',
job_type => 'STORED_PROCEDURE',
job_action => 'BEGIN DBMS_MVIEW.REFRESH("MVREMOTE");END;',
number_of_arguments => 0,
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY;',
end_date => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => 'This job refresh MVREMOTE every day'
);
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
BEGIN
*
ERROR at line 1:
ORA-27452: "BEGIN DBMS_MVIEW.REFRESH("MVREMOTE");END;" is an invalid name for a
database object.
ORA-06512: at "SYS.DBMS_ISCHED", line 175
ORA-06512: at "SYS.DBMS_SCHEDULER", line 286
ORA-06512: at line 2
Solution:
Specify the job type as ‘PLSQL_BLOCK’ and procedure complete successfully
kIsH@xHydra<>BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'MV_MVREMOTE_REFRESH1',
job_type => 'PLSQL_BLOCK',
job_action => 'EXEC DBMS_MVIEW.REFRESH("MVREMOTE");',
number_of_arguments => 0,
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY;',
end_date => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => 'This job refresh MVREMOTE every day'
);
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
PL/SQL procedure successfully completed.