ORA-12012: error on auto execute of job “KISH”.”MV_MVREMOTE_REFRESH1″
Cause:
*** 2023-05-01T19:05:19.376893+05:30 (CDB$ROOT(1))
*** SESSION ID:(73.30061) 2023-05-01T19:05:19.376926+05:30
*** CLIENT ID:() 2023-05-01T19:05:19.376932+05:30
*** SERVICE NAME:(SYS$USERS) 2023-05-01T19:05:19.376936+05:30
*** MODULE NAME:(DBMS_SCHEDULER) 2023-05-01T19:05:19.376940+05:30
*** ACTION NAME:(MV_MVREMOTE_REFRESH1) 2023-05-01T19:05:19.376945+05:30
*** CLIENT DRIVER:() 2023-05-01T19:05:19.376949+05:30
*** CONTAINER ID:(1) 2023-05-01T19:05:19.376953+05:30
ORA-12012: error on auto execute of job "KISH"."MV_MVREMOTE_REFRESH1"
ORA-06550: line 1, column 762:
PLS-00103: Encountered the symbol "DBMS_MVIEW" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "DBMS_MVIEW" to continue.
Solution:
Use EXEC in sqlplus but not in scheduler job actions
kIsH@xHydra<>col COMMENTS for a40
kIsH@xHydra<>SELECT job_name,job_creator,job_action,comments FROM dba_scheduler_jobs WHERE job_name LIKE '%MVREMOTE%';
JOB_NAME JOB_CREATOR JOB_ACTION COMMENTS
-------------------- -------------------- -------------------- ----------------------------------------
MV_MVREMOTE_REFRESH1 KISH EXEC DBMS_MVIEW.REF This job refresh MVREMOTE every day
RESH("MVREMOTE"); ;
Dropped the job and again recreated the job with ‘BEGIN <body of package block> END; ‘
kIsH@xHydra<>begin
2 dbms_scheduler.drop_job('MV_MVREMOTE_REFRESH1');
3 end;
4 /
PL/SQL procedure successfully completed.
BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'MV_MVREMOTE_REFRESH1',
job_type => 'PLSQL_BLOCK',
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;
/