PLS-00357: Table,View Or Sequence reference ‘MVREMOTE’ not allowed in this context

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

Leave a Reply

%d bloggers like this: