1

I want to make a oracle DB job which will run every minute and will call a stored procedure, the procedure will pick a cron expression from a configuration table and then check whether current systimestamp matches the cron or not, if it matches the cron then another stored procedure will be called which will do some db operations. Do we have any library in oracle to parse and compare the cron expressions?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Jack094
  • 11
  • 1
  • Why wouldn't you just use your job scheduler to run scripts directly? This seems quite convoluted. – Gordon Linoff Oct 01 '20 at 10:52
  • 1
    What about dbms_scheduler ? – Osamazx Oct 01 '20 at 12:08
  • What @Osamazx said: This is what dbms_scheduler was made for. Why re-invent the wheel? – pmdba Oct 01 '20 at 14:04
  • @pmdba change in frequency of dbms_scheduler requires ddl operation, my client is not comfortable with it, however if I provide facility to maintain a cron expression in a database table then they will only need to execute an update query for changing the frequency. – Jack094 Oct 01 '20 at 16:43
  • @Jack094 I don't think Oracle has a built-in cron interpreter, but the function `DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING` is very close to the calendaring syntax for RFC 2445 - see my answer [here](https://stackoverflow.com/a/58263744/409172) for an example. On the other hand, if you have access to the package `DBMS_SCHEDULER` you can just as easily change the repeat_interval - you don't need DDL to change jobs. – Jon Heller Oct 02 '20 at 04:25

1 Answers1

0

For your purpose can be used function F_CRON_NEXT_DATE from github https://github.com/frankiechapson/CRON-Next-Date

Darko Margetic
  • 21
  • 3
  • 1
    Please don't just post some tool or library as an answer. At least demonstrate [how it solves the problem](//meta.stackoverflow.com/a/251605) in the answer itself. – Luca Kiebel Feb 23 '22 at 09:42