0 Replies Latest reply on Apr 3, 2013 9:11 PM by 934896

    DBMS_SCHEDULER

    934896
      Hi

      I have 6 stored procedures that I want to execute at the same time I want to kick it off so the execution starts and then I want to execute all 6 after 20 minutes.

      I have written the following scheduler. Should this procedure kick off at start_date => v_start_date(sysdate), and thus execute every 20 minutes. Or having the dbms_scheduler.create_job not enough, Do we need anything else for the scheduler to start(execute).

      My Main issue is that I have 5 more stored procs like 'LOAD_APPLICATION_PKG.Refresh_X_Dim and I want all 6 to be exucuted at the same time and refresh every 20 minutes, same time.

      This particular assignment

      v_job_action := 'begin ' || 'LOAD_APPLICATION_PKG.Refresh_X_Dim;
                               end ;
                               commit;';
      schedules 'LOAD_APPLICATION_PKG.Refresh_X_Dim' to execute, can te other five stored procs be added so they all get processed parallelly(run at the same time)


      DECLARE
      v_loc varchar2(100);
      v_job_name varchar2(30);
      v_job_action varchar2(1000);
      v_start_date date;
      v_timeoffset number := 1;
      err_num number;
      err_msg varchar2(1000);

      BEGIN
      v_loc := 'schedule_a_job_START.1';

      v_start_date := SYSDATE;

      dbms_output.put_line('Starting time :'||to_char(sysdate,'mm/dd/yyyy hh:mi:ss PM'));

      v_loc := 'schedule_a_job_SET_JOB_NAME_ACTION.2';

      v_job_name := 'Refresh_Application_Dim' || '_' || to_char(sysdate, 'mmddyyhh24miss');

      v_job_action := 'begin ' || 'LOAD_APPLICATION_PKG.Refresh_X_Dim;
                               end ;
                               commit;';
      dbms_output.put_line('The Job Action is - '||v_job_action);

      dbms_scheduler.create_job(job_name => v_job_name,
      job_type => 'PLSQL_BLOCK',
      job_action => v_job_action,
      start_date => v_start_date,
                                         repeat_interval => 'FREQ = MINUTELY;INTERVAL = 30',
      job_class => 'DEFAULT_JOB_CLASS',
      enabled => TRUE,
      comments => 'Refresh Materialized views');
      commit;

      EXCEPTION
      WHEN OTHERS THEN
      err_num := SQLCODE;
      err_msg := SUBSTR(SQLERRM, 1, 1000);

      END;
      /

      I am new to scheduling please help me.

      Thanks
      Rajan