Forum Stats

  • 3,875,474 Users
  • 2,266,926 Discussions


Daily Email With Condition

Alter Boy
Alter Boy Member Posts: 65
edited Nov 9, 2018 1:25PM in APEX Discussions


I have an application that I want to improve slightly. It's designed to control subbing shifts for my part-time job; I just made it as a practice exercise. When someone posts a shift, someone else can comment on it, and the poster can assign that person to the shift.

I have a SHIFTS table with SHIFT_DATE and TAKEN_BY which hold the day of the shift and the person who took it respectively. Since people might post this weeks in advance, I want to be able to send an email to remind people the day before that they offered to sub this shift. The goal is to have something running at like let's say 6:00pm every day. So at 6:00pm, it will check through SHIFTS and send an email to all TAKEN_BY people where SHIFT_DATE - 1 = sysdate

I already have a send_email procedure so the actual emailing is not a problem, I'm just wondering if there is a way to get something to run every day at 6:00pm that does what I specified.

Thanks for any help,



Best Answer

  • cormaco
    cormaco Member Posts: 2,046 Silver Crown
    edited Nov 9, 2018 9:28AM Answer ✓

    You can use DBMS_SCHEDULER for this:


    And you should better check for this expression:

         trunc(shift_date) = trunc(sysdate) - 1

    If the time part of shift_date is zero you can leave out the trunc of this value.