Check oracle table for an expiry date and send mail

Question

I have a table pro where I have following fields

id, product name, email and expiry

I am trying to send an email from oracle when expiry date match with system date.

Is it possible through oracle jobs/scheduler or any other way?

Answer 1

You can use the query below to find a list of users. In my example, I have 120 days so modify it to fit your needs.

In addition, you will need a place to store the email address for each user than JOIN the tables and you can write some code to loop through the list and send mail.

Depending on your platform you can use utilities like mail or setup an Oracle mail server, which is a bit involved.

select username as “USER NAME”, expiry_date as “EXPIRE DATE”, account_status
from dba_users
where expiry_date < sysdate+120
and account_status IN ( ‘OPEN’, ‘EXPIRED(GRACE)’ )
order by account_status, expiry_date, username;