function to send notification

Question

I would like to send a message to each employee three months before the end of their contract

I try to do this as:

select hire_date from the emp table 

and compare it with sysdate then if hire date was less than the contract end date with 3 months then send notificatin end if

Answer 1

That shouldn't be a function, but a procedure. For example:

create or replace procedure p_end_of_contract_mail is
begin
  for cur_r in (select e_mail
                from emp
                where months_between(trunc(sysdate), end_of_contract_date) = 3
               ) 
  loop
  apex_mail.send(
    p_to   => cur_r.e_mail,
    p_from => 'noreply@my_application.com',
    p_body => 'Your contract ends in 3 months',
    p_subj => 'End of contract');
    
  apex_mail.push_queue;
end;
/

You'd schedule it so that it runs every day and automatically sends mail whenever necessary, e.g.

begin
   dbms_scheduler.create_job ( 
    job_name        => 'end_of_contract', 
    job_type        => 'PLSQL_BLOCK', 
    job_action      => 'p_end_of_contract_mail;',
    start_date      => SYSTIMESTAMP, 
    enabled         => true, 
    repeat_interval => 'FREQ=DAILY; INTERVAL=1'
   ); 
end;
/