ORA-12012: error on auto execute of job “KISH”.”MV_MVREMOTE_REFRESH1″

ORA-12012: error on auto execute of job “KISH”.”MV_MVREMOTE_REFRESH1″


*** 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.


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; ‘

  2  dbms_scheduler.drop_job('MV_MVREMOTE_REFRESH1');
  3  end;
  4  /

PL/SQL procedure successfully completed.

    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'

Leave a Reply

%d bloggers like this: