Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Schedule that runs every 15 minutes starting at half-hour

Rose ElleApr 11 2013 — edited Apr 15 2013

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

Locked Post

New comments cannot be posted to this locked post.

Locked on May 13 2013

Added on Apr 11 2013

#11g, #database, #database-key-features, #plsql, #scheduler

1 comment

2,777 views