Forum Stats

  • 3,769,313 Users
  • 2,252,947 Discussions
  • 7,874,985 Comments

Discussions

Timer Events

User_K3U3T
User_K3U3T Member Posts: 4 Green Ribbon
edited Apr 28, 2021 11:44AM in SQL & PL/SQL

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)?

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,916 Red Diamond

    RTFM DBMS_SCHEDULER.

    SY.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond

    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
    user10378862 Member Posts: 0 Green Ribbon

    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
    Solomon Yakobson Member Posts: 18,916 Red Diamond

    @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.