This discussion is archived
1 Reply Latest reply: Apr 15, 2013 1:57 AM by spajdy RSS

Schedule that runs every 15 minutes starting at half-hour

701800 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points