Oracle Database Discussions

Hi all, this is my first time trying to setup a job using dbms_scheduler. The script I've used to create everything is below. Everything created without error, and I can see the job and can see that it's running when it's supposed to. However, what the job is supposed to be doing isn't happening. I'm trying to manually refresh a materialized view after the the table it is based on is analyzed. The reason for this is that the time the table is analyzed varies from day to day, so I need to make sure the mat view isn't refreshed until after that process completes.

The problem appears that my program isn't calling the procedure. Because the procedure isn't doing anything. I have some statements built into my real procedure that insert values into a test table, and none of that is happening, so I can tell that the procedure isn't being called. The procedure works fine when I just run it straight with an "exec" command. Can anyone see what I might have done wrong? I think I might be using the arguments wrong or something, as the procedure has four parameters. Do I even need arguments? I didn't think I could set program_action => 'prc_refresh_mv ('schema345','tablename','schema123','matviewname')'. And I need the schedule and program modularized like they are (i.e., not built into the create_job statement), because other materialized view refreshes will use the same program and schedule.

Thanks in advance!
Mark

/*******************************************************
Create procedure to refresh materialized views.
********************************************************/
create or replace procedure schema123.prc_refresh_mv
(
  i_tbl_own in varchar2, -- table owner/schema
  i_tbl_nm  in varchar2, -- table name
  i_mv_own  in varchar2, -- mv owner/schema
  i_mv_nm   in varchar2  -- mv name
)
as
  v_last_analyzed_dt    date;
  v_last_mv_refresh_dt  date;
begin
  -- Get the last analyzed date for table.
  select  last_analyzed
  into    v_last_analyzed_dt
  from    sys.all_tables
  where   owner = i_tbl_own and table_name = i_tbl_nm;

  -- Get the last refresh date for materialized view.
  select  last_refresh_date
  into    v_last_mv_refresh_dt
  from    sys.all_mviews
  where   owner = i_mv_own and mview_name = i_mv_nm;

  -- If table has been analyzed
  if sysdate > v_last_analyzed_dt then
    -- If mv has not been refreshed
    if v_last_mv_refresh_dt < v_last_analyzed_dt then
      dbms_mview.refresh(i_mv_nm,'c');
    end if;
  end if;
end;

/*******************************************************
Create schedule components to refresh materialized views.
********************************************************/
begin
  -- Create single refresh schedule for all materialized views.
  dbms_scheduler.create_schedule (
    schedule_name   =>  'sch_mv_refresh_interval',
    start_date      =>  systimestamp,
    repeat_interval =>  'freq=daily; byhour=9,10,11; byminute=0,15,30,45',
    comments        =>  'Schedule to run daily every fifteen minutes between 9:00 AM and 11:45 AM.');

  -- Create single program for all materialized views
  dbms_scheduler.create_program (
    program_name        =>  'prg_refresh_mv',
    program_type        =>  'stored_procedure',
    program_action      =>  'prc_refresh_mv',
    number_of_arguments =>  4,
    enabled             =>  false,
    comments            =>  'Sceduled program to call procedure that refreshes materialized views');

  -- Create arguments needed for refresh procedure
  dbms_scheduler.define_program_argument (
    program_name      =>  'prg_refresh_mv',
    argument_position =>  1,
    argument_type     =>  'varchar2');

  dbms_scheduler.define_program_argument (
    program_name      =>  'prg_refresh_mv',
    argument_position =>  2,
    argument_type     =>  'varchar2');

  dbms_scheduler.define_program_argument (
    program_name      =>  'prg_refresh_mv',
    argument_position =>  3,
    argument_type     =>  'varchar2');

  dbms_scheduler.define_program_argument (
    program_name      =>  'prg_refresh_mv',
    argument_position =>  4,
    argument_type     =>  'varchar2');

  dbms_scheduler.enable('prg_refresh_mv');

  -- Connect program and schedule into job to refresh materialized view.
  dbms_scheduler.create_job (
    job_name      =>  'job_refresh_mv_mymatview',
    program_name  =>  'prg_refresh_mv',
    schedule_name =>  'sch_mv_refresh_interval',
    enabled       =>  false,
    auto_drop     =>  false,
    comments      =>  'Job to refresh materialized view mymatview.'
  );

  dbms_scheduler.set_job_argument_value (
    job_name          =>  'job_refresh_mv_mymatview',
    argument_position =>  1,
    argument_value    =>  'schema345');

  dbms_scheduler.set_job_argument_value (
    job_name          =>  'job_refresh_mv_mymatview',
    argument_position =>  2,
    argument_value    =>  'tablename');
 
  dbms_scheduler.set_job_argument_value (
    job_name          =>  'job_refresh_mv_mymatview',
    argument_position =>  3,
    argument_value    =>  'schema123');
  
  dbms_scheduler.set_job_argument_value (
    job_name          =>  'job_refresh_mv_mymatview',
    argument_position =>  4,
    argument_value    =>  'matviewname');

  dbms_scheduler.enable('job_refresh_mv_mymatview');
end;