Alter an oracle job to run every 5 seconds from a specific time till a specific time everyday

Question

EDIT: I need the below job to run from 11PM till 05:35AM, every 5 seconds in that window, everyday.

So, what I've done so far is set the following to run every 5 seconds.

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE (
    name => 'test_job',
    attribute => 'repeat_interval',
    value => 'freq=secondly;bysecond=5;'
);

END;

then the start_date and end_date to make it run from 9PM of today till 05:35AM of tomorrow.

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE (
    name => 'test_job',
    attribute => 'start_date',
    value => TO_TIMESTAMP_TZ('2018-01-29 21:00:00.000000000 EUROPE/BERLIN','YYYY-MM-DD HH24:MI:SS.FF TZR')
);

END;

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE (
    name => 'test_job',
   attribute => 'end_date',
    value => TO_TIMESTAMP_TZ('2018-01-30 05:35:00.000000000 EUROPE/BERLIN','YYYY-MM-DD HH24:MI:SS.FF TZR')
);

END;

Now the problem is that this is going to run once. I'm not sure how to make it run daily without giving up the every 5 second rule. I'm assuming I might need to use start_date and duration and omit end_date to achieve my goal, although I'm not sure how to specify duration.

Barbaros Özhan · Answer 1 · 2018-01-30T18:46:28.430

You may create such a job calling a stored procedure named pr_test performing some operations for every five seconds :

begin
dbms_scheduler.create_job (
   job_name           =>  'test_job',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'pr_test',
   start_date         =>  TO_TIMESTAMP_TZ('2018-01-30 21:00:00.000000000 EUROPE/BERLIN','YYYY-MM-DD HH24:MI:SS.FF TZR'),
   end_date           =>  TO_TIMESTAMP_TZ('2018-01-30 21:00:00.000000000 EUROPE/BERLIN','YYYY-MM-DD HH24:MI:SS.FF TZR') + (17/48), -- 8.5 hours
   repeat_interval    =>  'freq=secondly; interval=5;',
   enabled            =>  true); 
end;
/

If you want your scheduler job to be run everyday, it's more complicated with end time and interval definitions(considering today's date as start), without end_date, this works upto 06:00 am of the next day :

begin
dbms_scheduler.create_job (
   job_name           =>  'test_job',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'pr_test',
   start_date         =>  TO_TIMESTAMP_TZ('2018-01-30 21:00:00.000000000 EUROPE/BERLIN','YYYY-MM-DD HH24:MI:SS.FF TZR'),
   repeat_interval    =>  'freq=secondly;interval=5;byhour=21,22,23,0,1,2,3,4,5;byday=tue,wed,thu,fri,sat,sun,mon;',
   enabled            =>  true); 
end;
/

and may track from this view :

select * 
  from dba_scheduler_job_log l
 where l.job_name = 'TEST_JOB'
 order by l.log_date desc;

but this is only going to run till 2018-01-30 05:35:00 right? I need it to run from 11PM till 05:35AM, every 5 seconds in that window, everyday. I'm not sure how I can achieve this or if it is at all possible — GomuGomuZoro, Jan 30 '18 at 09:40
@GomuGomuZoro yes,exactly, this runs upto 2018-01-30 05:35:00. Please update your question to include everyday option. By the way, in this case end_date parameter would be meaningless. — Barbaros Özhan, Jan 30 '18 at 09:50
updated. Yes, I am trying to figure out how to edit the job to make it run daily, instead of using end date which lets it run only once. — GomuGomuZoro, Jan 30 '18 at 12:13

Blank · Answer 2 · 2018-01-29T15:18:12.263

The following PL/SQL block creates a lightweight job. Lightweight jobs must reference a program, and the program type must be 'PLSQL_BLOCK' or 'STORED_PROCEDURE'. In addition, the program must be already enabled when you create the job.

See sample for your reference. And please let me know if it helps.

BEGIN
      DBMS_SCHEDULER.CREATE_JOB (
       job_name         =>  'my_lightweight_job1',
       program_name     =>  'polling_prog_n2',
       repeat_interval  =>  'FREQ=SECONDLY;INTERVAL=5',
       end_date         =>  '30-JAN-18 05.35.00 AM',
       job_style        => 'LIGHTWEIGHT',
       comments         => 'Job that polls device n2 every 5 seconds');
    END;
    /

score 0 · Answer 3 · answered Sep 07 '22 at 18:55

In this case its best to create two separate schedules (no two jobs needed). Then use repeat_interval=>'v1,v2' as your schedule. See below

 set echo on
 
 begin
   dbms_scheduler.drop_schedule( schedule_name => 'v1');
   dbms_scheduler.drop_schedule( schedule_name => 'v2');
 end;
 /
 begin
   dbms_scheduler.create_schedule( schedule_name => 'v1',
      repeat_interval=>'FREQ=SECONDLY;INTERVAL=30;BYHOUR=0,1,2,3,4,23');
   dbms_scheduler.create_schedule( schedule_name => 'v2',
        repeat_interval => ' FREQ=SECONDLY;INTERVAL=30;BYHOUR=5;'|| 
  'BYMINUTE=0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20'||
   ',21,22,23,24,25,26,27,28,29,30,31,32,33,34,35');
 end;
 /
 set serveroutput on
 begin
   print_dates('v1,v2', to_timestamp_tz('01-JAN-2022 20:00:00',
                         'DD-MON-YYYY HH24:MI:SS'), 100);
   print_dates('v1,v2', to_timestamp_tz('02-JAN-2022 04:50:00',
                         'DD-MON-YYYY HH24:MI:SS'), 100);
 end;
 /

Note I used interval=30 to reduce the test output and below is the print_dates function that takes a repeat_interval as input and produces a list of execution dates for the repeat interval.

create or replace procedure print_dates 
 (
  cal_string in varchar2,
  start_date in timestamp with local time zone,
  nr_of_dates in pls_integer
 )
is
  date_after timestamp with local time zone := 
                   start_date - interval '1' second;
  next_execution_date timestamp with local time zone;
begin
  for i in 1 .. nr_of_dates
  loop
    dbms_scheduler.evaluate_calendar_string
     (cal_string, start_date, date_after, next_execution_date);

    dbms_output.put_line(to_char(next_execution_date,
                                 'DY DD-MON-YYYY (DDD-IW) HH24:MI:SS'));

    date_after := next_execution_date;
  end loop;
end;
/

And finally the output of code:

 SQL> SQL> SQL> SQL>   2    3    4    5  
 PL/SQL procedure successfully completed.
 
 SQL>   2    3    4    5    6    7    8  
 PL/SQL procedure successfully completed.
 
 SQL> SQL>   2    3    4    5  -->
 SAT 01-JAN-2022 (001-52) 23:00:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:00:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:01:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:01:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:02:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:02:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:03:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:03:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:04:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:04:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:05:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:05:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:06:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:06:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:07:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:07:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:08:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:08:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:09:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:09:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:10:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:10:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:11:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:11:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:12:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:12:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:13:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:13:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:14:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:14:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:15:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:15:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:16:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:16:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:17:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:17:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:18:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:18:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:19:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:19:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:20:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:20:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:21:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:21:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:22:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:22:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:23:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:23:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:24:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:24:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:25:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:25:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:26:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:26:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:27:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:27:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:28:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:28:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:29:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:29:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:30:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:30:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:31:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:31:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:32:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:32:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:33:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:33:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:34:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:34:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:35:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:35:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:36:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:36:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:37:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:37:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:38:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:38:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:39:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:39:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:40:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:40:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:41:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:41:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:42:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:42:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:43:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:43:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:44:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:44:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:45:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:45:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:46:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:46:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:47:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:47:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:48:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:48:30  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:49:00  +00 +00:00
 SAT 01-JAN-2022 (001-52) 23:49:30  +00 +00:00
 -->
 SUN 02-JAN-2022 (002-52) 04:50:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 04:50:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 04:51:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 04:51:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 04:52:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 04:52:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 04:53:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 04:53:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 04:54:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 04:54:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 04:55:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 04:55:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 04:56:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 04:56:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 04:57:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 04:57:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 04:58:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 04:58:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 04:59:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 04:59:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:00:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:00:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:01:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:01:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:02:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:02:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:03:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:03:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:04:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:04:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:05:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:05:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:06:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:06:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:07:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:07:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:08:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:08:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:09:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:09:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:10:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:10:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:11:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:11:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:12:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:12:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:13:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:13:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:14:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:14:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:15:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:15:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:16:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:16:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:17:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:17:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:18:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:18:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:19:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:19:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:20:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:20:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:21:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:21:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:22:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:22:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:23:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:23:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:24:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:24:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:25:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:25:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:26:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:26:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:27:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:27:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:28:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:28:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:29:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:29:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:30:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:30:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:31:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:31:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:32:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:32:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:33:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:33:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:34:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:34:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:35:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 05:35:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 23:00:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 23:00:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 23:01:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 23:01:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 23:02:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 23:02:30  +00 +00:00
 SUN 02-JAN-2022 (002-52) 23:03:00  +00 +00:00
 SUN 02-JAN-2022 (002-52) 23:03:30  +00 +00:00
 
 PL/SQL procedure successfully completed.

Alter an oracle job to run every 5 seconds from a specific time till a specific time everyday

3 Answers3