How to send email using APEX built-in procedures?

Oracle Application Express has the functionality to send send email from the applicationa using a database stored package called APEX_MAIL. It is built on top of Oracle’s UTL_SMTP package which has to be installed and made accessible to APEX for it to work. 

If you are the DBA, check the access privileges to UTL objects and grant the appropriate access to user/schema:

SELECT grantee, owner, table_name, grantor, privilege 
  FROM dba_tab_privs 
 WHERE table_name IN ('UTL_SMTP', 'UTL_TCP', 'DBMS_LOB');

APEX_MAIL has three procedures:

  • APEX_MAIL.SEND – for sending outbound mail from the application
  • APEX_MAIL.ADD_ATTACHMENT – to send an outbound mail from the app as an attachment (i.e. you might want to attach a PDF file stored in the database)
  • APEX_MAIL.PUSH_QUEUE – to push the messages stored in APEX_MAIL_QUEUE  

In the database, the application sending email will be using two tables to store and log the emails. These are:

  • APEX_MAIL_QUEUE – when we create an email, it will be stored in the queue table 
  • APEX_MAIL_LOG – when email is sent, the log table will contain information about the sent email, including the sender and recipient emails, subject, content of the email and a timestamp when the mail was sent. 

Since these stored procedures are called from the application context, to view the stored messages, push them from queue, first we need to set our database connection session to the appropriate context (otherwise we do not see any of the emails.  

  APEX_UTIL.set_security_group_id (workspace_id);

View the log:

  SELECT * 
    FROM apex_mail_log
ORDER BY last_updated_on DESC;

View the mail queue:

  SELECT * 
    FROM apex_mail_queue
ORDER BY last_updated_on DESC;