ORA-27452: “BEGIN DBMS_MVIEW.REFRESH(“MVREMOTE”);END;” is an invalid name for adatabase object.

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.