I have a requirement to schedule the procedure using Oracle DBA Scheduler to run on the 4th working day every month (excluding bank holidays and weekends). After doing some research it seems that Oracle DBA Scheduler does not recognize working days or bank holidays.

I have come up with below two ideas:

1. schedule another procedure to run on the 1st of every month and check when is the 4th working day and adjust the repeat_interval in the first scheduled job so it runs on 4th working day.

2. schedule the job to run monthly but only for the first 7 days, and in the run procedure create a check if today is 4th working day and if yes then execute if not do not run.

I do not have much experience with scheduling jobs, so I am not sure if option 1 is recommended, but it seems easier. I guess option 2 is still ok, but it will be more time-consuming to complete.

What option would you use? Or maybe something completely different?

Thanks

CodePudding user response：

You can have an SQL resulting with 4th working day in every month of the year. You should get holiday dates for the actual year.
Example:
Czech Republic Public Holidays 2022

Create a CTE holidays and another CTE named days holding 365 days with some attributes like day of week, is it a working day or not and distinct counters for working and non working days within a month:

WITH
    holidays AS
        (
            Select To_Date('01.01.2022', 'dd.mm.yyyy')  "A_DATE", 'New Year''s Day' "A_NAME" From Dual Union All
            Select To_Date('15.04.2022', 'dd.mm.yyyy')  "A_DATE", 'Good Friday ' "A_NAME"   From Dual Union All
            Select To_Date('18.04.2022', 'dd.mm.yyyy')  "A_DATE", 'Easter Monday' "A_NAME"  From Dual Union All
            Select To_Date('01.05.2022', 'dd.mm.yyyy')  "A_DATE", 'May Day ' "A_NAME"       From Dual Union All
            Select To_Date('08.05.2022', 'dd.mm.yyyy')  "A_DATE", 'Liberation Day' "A_NAME" From Dual Union All
            Select To_Date('05.07.2022', 'dd.mm.yyyy')  "A_DATE", 'St Cyril and St Methodius Day' "A_NAME" From Dual Union All
            Select To_Date('06.07.2022', 'dd.mm.yyyy')  "A_DATE", 'Jan Hus Day' "A_NAME"    From Dual Union All
            Select To_Date('28.09.2022', 'dd.mm.yyyy')  "A_DATE", 'Statehood Day' "A_NAME"  From Dual Union All
            Select To_Date('28.10.2022', 'dd.mm.yyyy')  "A_DATE", 'Independence Day' "A_NAME"   From Dual Union All
            Select To_Date('17.11.2022', 'dd.mm.yyyy')  "A_DATE", 'Freedom and Democracy Day' "A_NAME" From Dual Union All
            Select To_Date('24.12.2022', 'dd.mm.yyyy')  "A_DATE", 'Christmas Eve ' "A_NAME" From Dual Union All
            Select To_Date('25.12.2022', 'dd.mm.yyyy')  "A_DATE", 'Christmas Day' "A_NAME"  From Dual Union All
            Select To_Date('26.12.2022', 'dd.mm.yyyy')  "A_DATE", '2nd Day of Christmas ' "A_NAME" From Dual
        ),
    days AS
        (   Select 
                To_Date('01.01.' || To_Char(SYSDATE, 'yyyy'), 'dd.mm.yyyy')   (LEVEL - 1) "DATE_ID",

                To_Char(To_Date('01.01.' || To_Char(SYSDATE, 'yyyy'), 'dd.mm.yyyy')   (LEVEL - 1), 'DY') "DAY_OF_WEEK",
                CASE 
                    WHEN  To_Char(To_Date('01.01.' || To_Char(SYSDATE, 'yyyy'), 'dd.mm.yyyy')   (LEVEL - 1), 'DY') NOT IN('SAT', 'SUN') And 
                          To_Date('01.01.' || To_Char(SYSDATE, 'yyyy'), 'dd.mm.yyyy')   (LEVEL - 1) Not IN(Select A_DATE From holidays)
                    THEN 'YES' 
                ELSE '-' 
                END "WRKDAY",
                Count(*) OVER(Partition By  To_Char(To_Date('01.01.' || To_Char(SYSDATE, 'yyyy'), 'dd.mm.yyyy')   (LEVEL - 1), 'yyyymm') || 
                                            CASE 
                                                WHEN  To_Char(To_Date('01.01.' || To_Char(SYSDATE, 'yyyy'), 'dd.mm.yyyy')   (LEVEL - 1), 'DY') NOT IN('SAT', 'SUN') And 
                                                      To_Date('01.01.' || To_Char(SYSDATE, 'yyyy'), 'dd.mm.yyyy')   (LEVEL - 1) Not IN(Select A_DATE From holidays)
                                                THEN 'YES' 
                                            ELSE '-' 
                                            END  
                            Order By To_Date('01.01.' || To_Char(SYSDATE, 'yyyy'), 'dd.mm.yyyy')   (LEVEL - 1)) "DAY_NUM"
            From 
              Dual
            Connect By 
              LEVEL <= 365
            Order By
              LEVEL
        )

Now you can get all the dates of interest for year 2022:

Select
    DATE_ID, DAY_OF_WEEK, WRKDAY, DAY_NUM
From 
    days
Where
    WRKDAY = 'YES' And DAY_NUM = 4
Order By
    DATE_ID

The result is:

This dataset now can be used for any scheduling policy that you want. You just have to check if your actual date is listed within or you can use these dates to instruct the scheduler.
Regards...