1 Reply Latest reply: Apr 15, 2013 3:57 AM by spajdy RSS

    Schedule that runs every 15 minutes starting at half-hour

    Rose Elle
      Hello,

      I need help with creating a schedule that runs every 15 minutes from 6:30 PM to 7:30 PM. I wrote the following procedure to test based on the post {thread:id=1119591}.
      But, this runs at 6:00 PM, 6:15 PM and 7:45 PM as well. How do I prevent that?
      CREATE OR REPLACE PROCEDURE TEST_SCHEDULER AS
      
        start_date   TIMESTAMP;
        return_date_after TIMESTAMP;
        next_run_date        TIMESTAMP;
      
      BEGIN
        
        start_date := to_timestamp_tz(sysdate,'DD-MON-YYYY HH24:MI:SS');
        return_date_after := start_date;
        FOR i IN 1..20 LOOP
          DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('freq=minutely;byhour=6,7; byminute=0, 15, 30, 45', 
          start_date, return_date_after, next_run_date);
          DBMS_OUTPUT.PUT_LINE('next_run_date: ' || next_run_date);
          return_date_after := next_run_date;
       END LOOP;
       
      END TEST_SCHEDULER;
      
      
      
      SET SERVEROUTPUT ON;
      ALTER SESSION set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
      execute TEST_SCHEDULER2();
      
      
      ALTER SESSION set succeeded.
      anonymous block completed
      next_run_date: 12-APR-13 06.00.12.000000 AM
      next_run_date: 12-APR-13 06.15.12.000000 AM
      next_run_date: 12-APR-13 06.30.12.000000 AM
      next_run_date: 12-APR-13 06.45.12.000000 AM
      next_run_date: 12-APR-13 07.00.12.000000 AM
      next_run_date: 12-APR-13 07.15.12.000000 AM
      next_run_date: 12-APR-13 07.30.12.000000 AM
      next_run_date: 12-APR-13 07.45.12.000000 AM
      next_run_date: 13-APR-13 06.00.12.000000 AM
      next_run_date: 13-APR-13 06.15.12.000000 AM
      next_run_date: 13-APR-13 06.30.12.000000 AM
      next_run_date: 13-APR-13 06.45.12.000000 AM
      next_run_date: 13-APR-13 07.00.12.000000 AM
      next_run_date: 13-APR-13 07.15.12.000000 AM
      next_run_date: 13-APR-13 07.30.12.000000 AM
      next_run_date: 13-APR-13 07.45.12.000000 AM
      next_run_date: 14-APR-13 06.00.12.000000 AM
      next_run_date: 14-APR-13 06.15.12.000000 AM
      next_run_date: 14-APR-13 06.30.12.000000 AM
      next_run_date: 14-APR-13 06.45.12.000000 AM
      Thanks,
      Rose
        • 1. Re: Schedule that runs every 15 minutes starting at half-hour
          spajdy
          Do you mean something like this:
          BEGIN
          dbms_scheduler.create_schedule('sch_1', repeat_interval => 'freq=minutely;byhour=7;byminute=0, 15');
          dbms_scheduler.create_schedule('sch_2', repeat_interval => 'freq=minutely;byhour=6; byminute=30, 45');
          END; 
          /
          
          CREATE OR REPLACE PROCEDURE TEST_SCHEDULER AS
           
            start_date   TIMESTAMP;
            return_date_after TIMESTAMP;
            next_run_date        TIMESTAMP;
           
          BEGIN
            
            start_date := to_timestamp_tz(sysdate,'DD-MON-YYYY HH24:MI:SS');
            return_date_after := start_date;
            FOR i IN 1..20 LOOP
          --    DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('freq=minutely;byhour=6,7; byminute=0, 15, 30, 45', 
              DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('sch_1,sch_2',
              start_date, return_date_after, next_run_date);
              DBMS_OUTPUT.PUT_LINE('next_run_date: ' || next_run_date);
              return_date_after := next_run_date;
           END LOOP;
           
          END TEST_SCHEDULER;
          / 
           
           
          SET SERVEROUTPUT ON;
          ALTER SESSION set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
          
          BEGIN
            TEST_SCHEDULER;
          END;  
          /
          result are:
          next_run_date: 16.04.13 06:30:05,000000
          next_run_date: 16.04.13 06:45:05,000000
          next_run_date: 16.04.13 07:00:05,000000
          next_run_date: 16.04.13 07:15:05,000000
          next_run_date: 17.04.13 06:30:05,000000
          next_run_date: 17.04.13 06:45:05,000000
          next_run_date: 17.04.13 07:00:05,000000
          next_run_date: 17.04.13 07:15:05,000000
          next_run_date: 18.04.13 06:30:05,000000
          next_run_date: 18.04.13 06:45:05,000000
          next_run_date: 18.04.13 07:00:05,000000
          next_run_date: 18.04.13 07:15:05,000000
          next_run_date: 19.04.13 06:30:05,000000
          next_run_date: 19.04.13 06:45:05,000000
          next_run_date: 19.04.13 07:00:05,000000
          next_run_date: 19.04.13 07:15:05,000000
          next_run_date: 20.04.13 06:30:05,000000
          next_run_date: 20.04.13 06:45:05,000000
          next_run_date: 20.04.13 07:00:05,000000
          next_run_date: 20.04.13 07:15:05,000000