Oracle Scheduler Job Interval based on specific Function

In enterprise environments, it's common to have scheduled jobs that need to run at specific intervals during business hours. Managing these schedules efficiently ensures optimal resource utilization and adherence to operational policies. In this blog post, we'll explore a PL/SQL function that calculates the next execution time for a job based on configurable intervals and predefined working hours.

Overview

The function INTERVAL_BASED_ON_CONFIG calculates the next datetime when a job must be executed, considering:

Business Hours:
- Monday to Friday: 6:00 AM to 10:00 PM
- Saturday: 6:00 AM to 6:00 PM
- Sunday: No scheduled jobs
Configurable Interval: The execution interval is retrieved from a configuration parameter, defaulting to 60 minutes if not specified.

This function ensures that jobs are scheduled only during permitted times and can adapt to changes in the interval configuration without code modifications.

The Function Code

Here's the PL/SQL function:

CREATE OR REPLACE EDITIONABLE FUNCTION 
          INTERVAL_BASED_ON_CONFIG (pDate DATE DEFAULT SYSDATE)
RETURN DATE
-- Returns the next datetime when the job must be executed.
-- Mon-Fri: 6 AM - 10 PM, Sat: 6 AM - 6 PM
-- Retrieves the minute interval from configuration.

AS
    vReturnDate      DATE;
    vMinutesInterval NUMBER := p_adm.get_param('interval_for_adm_jobs', 60);

BEGIN
    -- Ensure day abbreviations are in English to avoid locale issues
    DECLARE
        vDay  VARCHAR2(3) := TO_CHAR(pDate, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH');
        vHour NUMBER      := TO_NUMBER(TO_CHAR(pDate, 'HH24'));
    BEGIN
        IF vDay NOT IN ('SAT', 'SUN') THEN
            -- Monday to Friday
            IF vHour BETWEEN 6 AND 22 THEN
                -- Between 6:00 AM and 10:00 PM
                vReturnDate := TRUNC(pDate, 'MI') + vMinutesInterval / 1440;
            ELSIF vHour < 6 THEN
                -- Before 6:00 AM
                vReturnDate := TRUNC(pDate) + 6 / 24 + vMinutesInterval / 1440;
            ELSE
                -- After 10:00 PM
                vReturnDate := TRUNC(pDate) + 1 + 6 / 24 + vMinutesInterval / 1440;
            END IF;
        ELSIF vDay = 'SAT' THEN
            -- Saturday
            IF vHour BETWEEN 6 AND 18 THEN
                -- Between 6:00 AM and 6:00 PM
                vReturnDate := TRUNC(pDate, 'MI') + vMinutesInterval / 1440;
            ELSIF vHour < 6 THEN
                -- Before 6:00 AM
                vReturnDate := TRUNC(pDate) + 6 / 24 + vMinutesInterval / 1440;
            ELSE
                -- After 6:00 PM
                vReturnDate := NEXT_DAY(TRUNC(pDate), 'MONDAY') + 6 / 24 + vMinutesInterval / 1440;
            END IF;
        ELSE
            -- Sunday
            vReturnDate := NEXT_DAY(TRUNC(pDate), 'MONDAY') + 6 / 24 + vMinutesInterval / 1440;
        END IF;
    END;

    RETURN vReturnDate;
END;
/

How the Function Works

Let's look into the details of how this function operates.

Parameters and Variables

pDate DATE DEFAULT SYSDATE: The reference date and time from which the next execution time is calculated. Defaults to the current system date and time.
vReturnDate DATE: The variable that will store the calculated next execution datetime.
vMinutesInterval NUMBER: The execution interval in minutes, fetched from the configuration using p_adm.get_param. If the parameter is not set, it defaults to 60 minutes.

Locale Handling

To prevent issues with date formats in different locales, we specify the NLS (National Language Support) parameter:

vDay VARCHAR2(3) := TO_CHAR(pDate, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH');

This ensures that the day abbreviations are consistent, regardless of the database session's language settings.

Main Logic

The function uses nested IF statements to determine the next execution time based on the day of the week and the current hour.

Monday to Friday

IF vDay NOT IN ('SAT', 'SUN') THEN
    -- Monday to Friday
    IF vHour BETWEEN 6 AND 22 THEN
        -- Between 6:00 AM and 10:00 PM
        vReturnDate := TRUNC(pDate, 'MI') + vMinutesInterval / 1440;
    ELSIF vHour < 6 THEN
        -- Before 6:00 AM
        vReturnDate := TRUNC(pDate) + (6 / 24) + (vMinutesInterval / 1440);
    ELSE
        -- After 10:00 PM
        vReturnDate := TRUNC(pDate) + 1 + (6 / 24) + (vMinutesInterval / 1440);
    END IF;

Between 6:00 AM and 10:00 PM: Adds the interval to the current time, ensuring minute-level precision with TRUNC(pDate, 'MI').
Before 6:00 AM: Sets the next execution time to 6:00 AM on the same day plus the interval.
After 10:00 PM: Sets the next execution time to 6:00 AM on the following day plus the interval.

Saturday

ELSIF vDay = 'SAT' THEN
    -- Saturday
    IF vHour BETWEEN 6 AND 18 THEN
        -- Between 6:00 AM and 6:00 PM
        vReturnDate := TRUNC(pDate, 'MI') + vMinutesInterval / 1440;
    ELSIF vHour < 6 THEN
        -- Before 6:00 AM
        vReturnDate := TRUNC(pDate) + (6 / 24) + (vMinutesInterval / 1440);
    ELSE
        -- After 6:00 PM
        vReturnDate := NEXT_DAY(TRUNC(pDate), 'MONDAY') + (6 / 24) + (vMinutesInterval / 1440);
    END IF;

Between 6:00 AM and 6:00 PM: Schedules the next execution during Saturday's operational hours.
Outside Operational Hours: If it's before 6:00 AM or after 6:00 PM on Saturday, the next execution is set to Monday at 6:00 AM plus the interval.

Sunday

ELSE
    -- Sunday
    vReturnDate := NEXT_DAY(TRUNC(pDate), 'MONDAY') + (6 / 24) + (vMinutesInterval / 1440);

All Day: No jobs are scheduled on Sunday. The next execution is set to Monday at 6:00 AM plus the interval.

Time Calculations

TRUNC(pDate, 'MI'): Truncates the datetime to the nearest minute, removing seconds and milliseconds.
vMinutesInterval / 1440: Converts minutes to fractional days (since there are 1,440 minutes in a day).
6 / 24: Represents 6 hours as a fraction of a day (since there are 24 hours in a day).

By adding these fractions to the date, we manipulate the datetime values accurately for scheduling.

Configurable Interval

The function retrieves the execution interval using:

vMinutesInterval NUMBER := p_adm.get_param('interval_for_adm_jobs', 60);

p_adm.get_param: A custom function that fetches the parameter value from a configuration table or source.
Default Value: If the parameter is not found, it defaults to 60 minutes.

This design allows administrators to change the job execution frequency without altering the code.

How too use it in Scheduler Job?

Just replace the Interval Syntax with this function name:
     repeat_interval => 'INTERVAL_BASED_ON_CONFIG'

Practical Applications

This function is particularly useful in scenarios where:

Resource Management: Running jobs during non-peak hours to optimize system performance.
Operational Compliance: Ensuring jobs don't interfere with business operations during restricted times.
Flexibility: Allowing easy adjustment of execution intervals through configuration.

Potential Enhancements

While the current function is robust, there are opportunities for improvement:

Holiday Scheduling: Incorporate a holiday calendar to skip scheduling on public holidays.
Dynamic Business Hours: Fetch operational hours from configuration to adapt to changes without code updates.
Time Zone Support: Adjust for different time zones if the application serves multiple regions.

Conclusion

The INTERVAL_BASED_ON_CONFIG function provides a flexible and efficient way to schedule jobs within specified time frames. By leveraging configurable parameters and precise datetime calculations, it ensures that jobs run when intended without manual intervention.

This approach not only enhances operational efficiency but also reduces the risk of running critical jobs during unintended periods.

Alternative?

I tried really to handle this without an UDF but I cant mix a schedule and a window within an oracle scheduler job.. or.. i didnt check the way?

If you would do this I end up with 2 Schedules + 2 Jobs:

begin

DBMS_SCHEDULER.CREATE_SCHEDULE(
        schedule_name   => 'TOMS_WEEKDAY_SCHEDULE',
        repeat_interval => 'FREQ=MINUTELY; INTERVAL=8; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=6-22; BYMINUTE=0,8,16,24,32,40,48,56; BYSECOND=0',
        comments        => 'Runs every 8 minutes from 6 AM to 10 PM on Monday to Friday'
    );

DBMS_SCHEDULER.CREATE_SCHEDULE(
        schedule_name   => 'TOMS_SATURDAY_SCHEDULE',
        repeat_interval => 'FREQ=MINUTELY; INTERVAL=8; BYDAY=SAT; BYHOUR=6-17; BYMINUTE=0,8,16,24,32,40,48,56; BYSECOND=0',
        comments        => 'Runs every 8 minutes from 6 AM to 6 PM on Saturday'
    );
end;
/

begin 
  DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'MY_JOB_SATURDAY',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN
                               -- Your PL/SQL code here
                            END;',
        schedule_name   => 'TOMS_SATURDAY_SCHEDULE',
        enabled         => TRUE,
        auto_drop       => FALSE,
        comments        => 'Job runs every 8 minutes from 6 AM to 6 PM on Saturday'
    );
/

begin 
  DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'MY_JOB_WEEKDAY',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN
                               -- Your PL/SQL code here
                            END;',
        schedule_name   => 'TOMS_WEEKDAY_SCHEDULE',
        enabled         => TRUE,
        auto_drop       => FALSE,
        comments        => 'Job runs every 8 minutes from 6 AM to 6 PM on Saturday'
    );
/
-- Change the minutes.. 
BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
        name      => 'TOMS_SCHEDULE_NAME',
        attribute => 'repeat_interval',
        value     => 'FREQ=MINUTELY; INTERVAL=N; BYDAY=DAY_LIST; BYHOUR=START_HOUR-END_HOUR'
    );
END;
/

References

Oracle Date/Time Functions: Oracle Documentation
PL/SQL Best Practices: Oracle PL/SQL Guidelines
Datetime Arithmetic in Oracle: Understanding Date Arithmetic