DBMS_SCHEDULER next run date

user_17327590 0
Viewed 2243

Good day. A little background ... There was an Oracle 12.2 database. It used the DBMS_JOBS packages and used the expression dbms_job.next_date (job, nvl (datetime, sysdate)); Recently migrated DB to Oracle 19 Please tell me how in DBMS_SCHEDULER the next start of the job appears? Is it possible to update the date of the next launch, substituting the date in the format 'DD.MM.YYYY HH24: MI: SS' from a previously prepared table or from a function that determines when it is necessary to start a job?

dbms-scheduler oracle19c oracle
3 Answers

In DBMS_SCHEDULER you can create even complex schedule times, see example calculate hours based on business hours in Oracle SQL

How do you run dbms_job.next_date? Perhaps instead of setting the start time of your job, just execute the job manually with DBMS_SCHEDULER.RUN_JOB(job_name, FALSE);.

Or create a Scheduler Job without start time

BEGIN
    DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => '<job_name>'
      ,start_date      => NULL
      ,repeat_interval => NULL
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => '<PL/SQL procedure>'
    );
    DBMS_SCHEDULER.SET_ATTRIBUTE
    ( NAME      => '<job_name>'
     ,attribute => 'AUTO_DROP'
     ,VALUE     => FALSE);
END;

And then set the start time when you like to run it:

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => '<job_name>'
     ,attribute => 'START_DATE'
     ,value     => TIMESTAMP '2021-11-05 12:30:00');
END;

Just another note, a DATE or TIMESTAMP does not have any format as such. The values are stored as internal byte values, what you see is the (default) output format according to current session NLS_DATE_FORMAT / NLS_TIMESTAMP_FORMAT. You should never store date/time values as string, it's a design flaw.

If you just like to know, when your jobs runs the next time, run this query:

SELECT JOB_NAME, START_DATE, END_DATE, LAST_START_DATE, NEXT_RUN_DATE
FROM ALL_SCHEDULER_JOBS
WHERE JOB_NAME = '<job_name>';
user_30272660

As far as I can tell, there's no next_date there. Scheduler uses (as documentation says)

rich calendaring syntax to enable you to define repeating schedules, such as "every Tuesday and Friday at 4:00 p.m." or "the second Wednesday of every month." This calendaring syntax is used in calendaring expressions in the repeat_interval argument of a number of package subprograms.

According to that, I'm not sure you can easily "convert" dates stored in your table into such a calendar.

You could, though, schedule a job that runs only once, at a time fetched from your table, and use it as scheduler's start_date parameter.

user_90979060

As a result, I did the following: Created, in which the time of the next job start is picked up from the tuning table.

 
.....................
    declare
    planner_time DATE :=to_date(app_plan.svc.get_setting('PLANNER_NEXT_TIME'),'DD.MM.YYYY HH24:MI:SS');
.....................        
        IF SYSDATE> = planner_time
    THEN
    dbms_scheduler.run_job (job_name => JOB_NAME || PLANNER_ID);
    END IF;
.....................

And I put this procedure in a new job that runs every 2 minutes

user_173275900
Related