Timer Events

User_K3U3T

Is it possible to make a procedure/trigger that runs every day at 00:00, for example, a procedure that updates the subscription of the clients (to check if the subscription ended)?

Solomon Yakobson

RTFM DBMS_SCHEDULER.

SY.

Frank Kulash

HI, @User_K3U3T

You can use the Oracle-supplied package dbms_scheduler to run a procedure at fixed times. It's documented in the Packages and Types manual:

DBMS_SCHEDULER (oracle.com)

user10378862

I recommend that you check the manual like @Frank Kulash mentioned, there you can find all that you need

but to get you started you can create a job like this:

begin

 dbms_scheduler.create_job (

  job_name    => 'RUN_MY_STORED_PROCEDURE',

  job_type    => 'STORED_PROCEDURE',

  job_action   => 'schema.my_package.my_stored_procedure();',

  start_date   => to_date('10-03-2021 16:00','dd-mm-yyyy hh24:mi'),

  repeat_interval => 'FREQ=DAILY; BYHOUR=06,08,10,12,14,16,18;', 

  enabled     => true,

  comments    => 'Run job every other hour between 06:00 and 18:00');

end;

/

And to test different intervals you can run this and just change interval input

declare

  startdate date;

  dateafter date;

  nextdate date;

begin

  startdate := TO_DATE('01-11-2014 10:00','DD-MM-YYYY HH24:MI');

  dateafter := startdate;

  for i in 1..24 loop

   dbms_scheduler.evaluate_calendar_string(

     'FREQ=MONTHLY;BYMONTHDAY=1,16;BYHOUR=10,17;BYSETPOS=1,4'

    , startdate

    , dateafter

    , nextdate

   );

   dbms_output.put_line(to_char(nextdate,'YYYY-MM-DD HH24:MI'));

   dateafter := nextdate;

  end loop;

end;

/

Erlendur

Solomon Yakobson

@user10378862: I recommend that you check the manual 

And that manual explains how start_date works:

The calendaring syntax does not allow you to specify a time zone. Instead the Scheduler retrieves the time zone from the start_date argument. If jobs must follow daylight savings adjustments, then you must specify a region name for the time zone of the start_date.

And since you didn't specify time zone job will run one hour off at next DST point.

SY.