EVALUATE_CALENDAR_STRING v11

Use the EVALUATE_CALENDAR_STRING procedure to evaluate the repeat_interval value specified when creating a schedule with the CREATE_SCHEDULE procedure. The EVALUATE_CALENDAR_STRING procedure will return the date and time that a specified schedule will execute without actually scheduling the job.

The signature of the EVALUATE_CALENDAR_STRING procedure is:

EVALUATE_CALENDAR_STRING(
  <calendar_string> IN VARCHAR2,
  <start_date> IN TIMESTAMP WITH TIME ZONE,
  <return_date_after> IN TIMESTAMP WITH TIME ZONE,
  <next_run_date> OUT TIMESTAMP WITH TIME ZONE)

Parameters

calendar_string

calendar_string is the calendar string that describes a repeat_interval that is being evaluated.

start_date IN TIMESTAMP WITH TIME ZONE

start_date is the date and time after which the repeat_interval will become valid.

return_date_after

Use the return_date_after parameter to specify the date and time that EVALUATE_CALENDAR_STRING should use as a starting date when evaluating the repeat_interval.

For example, if you specify a return_date_after value of 01-APR-13 09.00.00.000000, EVALUATE_CALENDAR_STRING will return the date and time of the first iteration of the schedule after April 1st, 2013.

next_run_date OUT TIMESTAMP WITH TIME ZONE

next_run_date is an OUT parameter that will contain the first occurrence of the schedule after the date specified by the return_date_after parameter.

Example

The following example evaluates a calendar string and returns the first date and time that the schedule will be executed after June 15, 2013:

DECLARE
  result     TIMESTAMP;
BEGIN

  DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING
  (
    'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=17;',
    '15-JUN-2013', NULL, result
  );

    DBMS_OUTPUT.PUT_LINE('next_run_date: ' || result);
END;
/

next_run_date: 17-JUN-13 05.00.00.000000 PM

June 15, 2013 is a Saturday; the schedule will not execute until Monday, June 17, 2013 at 5:00 pm.