Sounds like this would not be a Page Process, but a apex_plsql_job/dbms_job in the background.
Apex Mail doc : APEX_MAIL
Apex PLSQL Job doc : http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_plsql_job.htm#AEAPI1203
Does that get you pointed in the right direction?
I have already gone through the link but couldn't figure out how to make use of it. Can you elaborate a bit. My need to check if today is the birthday of an employee. Then his manager is notified through mails.
Do you need help on the query to get people's birthday today, or do you need help sending the email?
I have a database which contains Employee's birthday. My requirements is to check the date and month with today's date and then send the mail.
I need help in sending the mail with the mentioned requirements.
1 person found this helpful
Well I just googled "Oracle Todays Birthdays" and hit this link:
Break the requirement into pieces. Work on getting today's birthdays in a query - once you have that working, post back and someone can help you with the mail/job piece.
1 person found this helpful
First you need to write a procedure to send e-mails. Please find sample code below to send e-mail to manager, if employee birthday is today
CREATE OR REPLACE PROCEDURE pr_send_birthday_alerts IS BEGIN /* if V_APP_USER is null, procedure is being executed as job set security context */ IF v('APP_USER') IS NULL THEN wwv_flow_api.set_security_group_id; END IF; -- Trigger mails if employee birthday is today -- Send mail to Manager email address /* Please change with actual table and column values */ FOR i IN (SELECT emp.name emp_name, manager.email manager_email, manager.name manager_name FROM t_employee emp, t_employee manager WHERE emp.manager_id = manager.emp_id AND TRUNC(emp.birth_date) = TRUNC(SYSDATE)) LOOP /* Send mail */ /* Please change Subject and Body according to your need */ APEX_MAIL.SEND( p_to => i.manager_email, p_cc => NULL, p_from => 'firstname.lastname@example.org', p_subj => 'Today is '||i.emp_name||' birthday', p_body => NULL ); END LOOP; -- push all emails from queue APEX_MAIL.PUSH_QUEUE; END pr_send_birthday_alerts;
Next you need to schedule a job which will run above procedure everyday to Send Birthday alerts. Please find sample code below to schedule a procedure to run as Job.
BEGIN /* Change Start date according to your requirement */ DBMS_SCHEDULER.CREATE_SCHEDULE ( schedule_name => 'sch_every_day', start_date => TO_TIMESTAMP_TZ(TO_CHAR(SYSDATE,'YYYY-MM-DD')||' 06:00:00 +02:00','YYYY-MM-DD HH:MI:SS TZH:TZM'), repeat_interval => 'FREQ=DAILY;' ); DBMS_SCHEDULER.CREATE_JOB ( job_name => 'job_every_day', schedule_name => 'sch_every_day', job_type => 'STORED_PROCEDURE', job_action => 'pr_send_birthday_alerts', enabled => TRUE ); COMMIT; END;
So everyday in morning 6:00 A.M. GMT+ 2, system will check if any employee birthday is today and will send alert to his/her manager.
Please note that these code is not tested.
Thank you very much for your code. I am yet to try it. But I am sure it would help me solve my problem.
Thanks once again. Will let you know if it has worked fine for me.