PLS-00357: Table,View Or Sequence reference ‘MVREMOTE’ not allowed in this context
Cause:
Syntax issue
*** 2023-05-01T21:13:50.353139+05:30 (CDB$ROOT(1))
*** SESSION ID:(80.21755) 2023-05-01T21:13:50.353161+05:30
*** CLIENT ID:() 2023-05-01T21:13:50.353165+05:30
*** SERVICE NAME:(SYS$USERS) 2023-05-01T21:13:50.353167+05:30
*** MODULE NAME:(DBMS_SCHEDULER) 2023-05-01T21:13:50.353170+05:30
*** ACTION NAME:(MV_MVREMOTE_REFRESH1) 2023-05-01T21:13:50.353172+05:30
*** CLIENT DRIVER:() 2023-05-01T21:13:50.353175+05:30
*** CONTAINER ID:(1) 2023-05-01T21:13:50.353177+05:30
ORA-12012: error on auto execute of job "KISH"."MV_MVREMOTE_REFRESH1"
ORA-06550: line 1, column 783:
PLS-00357: Table,View Or Sequence reference 'MVREMOTE' not allowed in this context
ORA-06550: line 1, column 763:
PL/SQL: Statement ignored
~
Solution:
When specifying action for the mview refresh using scheduler jobs, double quotes should not be used inside DBMS_MVIEW.REFRESH package. Drop the job first and recreate it with single quotes.
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 BEGIN DBMS_MVIEW.REF This job refresh MVREMOTE every day
RESH("MVREMOTE");END
;
kIsH@xHydra<>conn kish/password
Connected.
kIsH@xHydra<>begin
2 dbms_scheduler.drop_job('MV_MVREMOTE_REFRESH1');
3 end;
4 /
PL/SQL procedure successfully completed.
kIsH@xHydra<>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;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
PL/SQL procedure successfully completed.
kIsH@xHydra<>col CURRMVOWNER for a20
kIsH@xHydra<>col CURRMVNAME for a20
kIsH@xHydra<>select * from v$mvrefresh;
SID SERIAL# CURRMVOWNER CURRMVNAME CON_ID
---------- ---------- -------------------- -------------------- ----------
77 51278 KISH MVREMOTE 1