7 Replies Latest reply on Jul 31, 2013 11:13 AM by Saumyadip Sarkar-Oracle

    Sending Mails Using APEX 4.2

    Saumyadip Sarkar-Oracle

      Hi All


      I am in need to send emails using APEX 4.2. I have seen under page processing there is option called Send Email. But really not sure how to make use of it. My requirement is to trigger a mail on employee's birthday. Following is the screenshot.

      Can anyone help me on this. Any reply would highly be appreciable.



      • Process Type
      • Process Attributes
      • Process
      • Messages
      • Process Conditions


      Create Page Process - Process Type



      3 - Edit AR Employees

      Select the category of the process you wish to create:
      Reset Pagination
      Reset Pagination
      Session State
      Session State
      Data Manipulation
      Data Manipulation
      Web Services
      Web Services
      Form Pagination
      Form Pagination
      Send E-Mail
      Send E-Mail
      Close popup window
      Close popup window
      Run On Demand Process
      Run On Demand Process


        • 1. Re: Sending Mails Using APEX 4.2
          Russ C

          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?

          • 2. Re: Sending Mails Using APEX 4.2
            Saumyadip Sarkar-Oracle

            Hi RussC

            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.

            • 3. Re: Sending Mails Using APEX 4.2

              Do you need help on the query to get people's birthday today, or do you need help sending the email?

              • 4. Re: Sending Mails Using APEX 4.2
                Saumyadip Sarkar-Oracle

                Hi Sc0tt


                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.

                • 5. Re: Sending Mails Using APEX 4.2

                  Well I just googled "Oracle Todays Birthdays" and hit this link:


                  database - Selecting employees with birthdays in given range using Oracle SQL - Stack Overflow


                  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
                  • 6. Re: Sending Mails Using APEX 4.2



                    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
                      /* if V_APP_USER is null, procedure is being executed as job
                      set security context */
                      IF v('APP_USER') IS NULL THEN
                      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))
                      /* Send mail */
                      /* Please change Subject and Body according to your need */
                      p_to        => i.manager_email,
                      p_cc => NULL,
                      p_from      => 'no.reply@yourdomain.com',
                      p_subj      => 'Today is '||i.emp_name||' birthday',
                      p_body      => NULL
                      END LOOP;
                      -- push all emails from 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.


                      /* 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


                    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.




                    1 person found this helpful
                    • 7. Re: Sending Mails Using APEX 4.2
                      Saumyadip Sarkar-Oracle

                      Hi Hari


                      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.