SQL & PL/SQL

Hi,

I am trying to refresh a materialized view using the DBMS_SCHEDULER job. But the refresh is not occurring for the MView.

BEGIN

DBMS_SCHEDULER.CREATE_JOB

(

JOB_NAME            => 'REFRESH_MVIEW',

JOB_TYPE            => 'PLSQL_BLOCK',

JOB_ACTION          => 'BEGIN EXEC DBMS_MVIEW.REFRESH("TESTR7"); END;',

NUMBER_OF_ARGUMENTS => 0,

START_DATE          => '01-MAR-2018 10:32:00 AM',

REPEAT_INTERVAL     => 'FREQ=MINUTELY;BYHOUR=3; BYMINUTE=39;',

END_DATE            => NULL,

ENABLED             => TRUE,

AUTO_DROP           => FALSE,

COMMENTS            => 'JOB TO REFRESH'

);

END;

create materialized view testr7

build immediate

refresh complete

disable query rewrite

as

select * from table (all_emps);

create or replace

function all_emps return emp_tab

   is

   l_emp_tab emp_tab := emp_tab();

   n integer := 0;

    begin

       for r in (select empno, ename from emp)

       loop

          l_emp_tab.extend;

          n := n + 1;

        l_emp_tab(n) := emp_obj(r.empno, r.ename);

      end loop;

      return l_emp_tab;

   end;

Thanks in advance.

Regards,

Rupa