7 Replies Latest reply: Jul 31, 2013 6:13 AM by Saumyadip Sarkar RSS

    Sending Mails Using APEX 4.2

    Saumyadip Sarkar

      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:
      PL/SQL
      PL/SQL
      Reset Pagination
      Reset Pagination
      Plug-ins
      Plug-ins
      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

            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
              Sc0tt

              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

                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
                  Sc0tt

                  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.

                  • 6. Re: Sending Mails Using APEX 4.2
                    Hari_639

                    Hello,

                     

                    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      => 'no.reply@yourdomain.com',
                      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.

                     

                    Regards,

                    Hari

                    • 7. Re: Sending Mails Using APEX 4.2
                      Saumyadip Sarkar

                      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.