How to send mail from Oracle APEX

In this tutorial, I will show you how to send mail from Oracle APEX. Let’s start…

This tutorial divide into tree parts-

  1. Configure mail server
  2. Enabling network services
  3. Test mail

1. Configure mail server: Before you configure Oracle APEX mail server you need to a SMTP mail server username and password. Then login your oracle apex admin panel. After login Oracle APEX admin panel then go to manage instance > instance settings > mail.

Screenshot

Configure mail server as below:

Application Express Instance URL: APEX base URL
Application Express Images URL: APEX static file location
SMTP Host Address: Host server (eg. smtp.example.com)
SMTP Host Port: Mail server port (eg. 25)
SMTP Authentication Username: your mail server username
SMTP Authentication Password: your mail server password	
Use SSL/TLS: Optional
Default Email From Address: Optional
Maximum Emails per Workspace: Optional

2. Enabling Network Service: By default, the ability to interact with network services is disabled in Oracle Database 11g or later. Therefore, you must use the BMS_NETWORK_ACL_ADMIN package to grant connect privileges to any host for the database user. In this tutorial i will create network service for APEX 4.x power user that means APEX_040200 (Power user for APEX 5 is APEX_050000):

DECLARE
  ACL_PATH VARCHAR2(4000);
BEGIN
    -- Look for the ACL currently assigned to '*' and give APEX_040200
    -- the "connect" privilege if APEX_040200
    -- does not have the privilege yet.
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
    WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE (ACL_PATH,'APEX_040200','connect') IS NULL THEN
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,'APEX_040200', TRUE, 'connect');
  END IF;
  EXCEPTION
    -- When no ACL has been assigned to '*'.
  WHEN NO_DATA_FOUND THEN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
    'ACL that lets power users to connect to everywhere',
    'APEX_040200', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

3. Send Mail: Oracle application express send mail using APEX_MAIL package. example…

APEX_MAIL.SEND(
        p_to        => 'email@gmail.com',
        p_from      => 'email2@oracle.com',
        p_subj      => 'APEX_MAIL with attachment',
        p_body      => 'Please review the attachment.',
        p_body_html => '<b>Please</b> review the attachment');
/

Send mail with attachments…

DECLARE
    l_id Number;
    x varchar2(100);
    y BLOB;
    z varchar2(100);
BEGIN
    l_id := APEX_MAIL.SEND(
        p_to        => 'email@gmail.com',
        p_from      => 'email2@oracle.com',
        p_subj      => 'APEX_MAIL with attachment',
        p_body      => 'Please review the attachment.',
        p_body_html => '<b>Please</b> review the attachment');

    SELECT FILENAME, IMAGE, MIMETYPE into x, y, z
        FROM CUSTOMER
        WHERE CUSTOMER_ID = 100;

        APEX_MAIL.ADD_ATTACHMENT(
            p_mail_id    => l_id,
            p_attachment => y,   -- BLOB column name
            p_filename   => x,   -- File Name 
            p_mime_type  => z);  -- MIME column name
END;
/

Thanks for visit. Happy apexing……

Published by

Ali Asgor

Graduate in GES, OCP holder, Self-motivated app developer, Programming lover, Lazy person...

14 thoughts on “How to send mail from Oracle APEX”

    1. If u use gmail as mail server, then configuration like
      SMTP server: smtp.gmail.com
      SMTP username: Your full gmail address (e.g. example@gmail.com)
      SMTP password: Your Gmail password
      SMTP port: 465

      Or if own mail server, then configure like
      SMTP server: mail.yourdomain.com or smtp.yourdomain.com
      SMTP username: Your full email address (e.g. example@yourdomain.com)
      SMTP password: Your email password
      SMTP port: 25/26

      1. Does this only need to apply to the original server?
        Such as mine if I am building it, or will it need to change with the server it is on?
        My users use many different servers between themselves.

  1. Dear !
    But i got error : ORA-29024: Certificate validation failure
    I use zoho mail. and port 465 (SSL)

    How to resolve it? Could you help me?
    Thanks

    1. You need to install a certificate on APEX Admin panel. U can use oracle wallet manager to install a certificate. Settings through Manage Instance > Instance Setting > Wallet

  2. I’m getting this error
    ORA-29279: SMTP permanent error: 530 5.7.0 Must issue a STARTTLS command first. f2sm21861492wjr.2 – gsmtp

    My Apex Release is 4.2.6 and Database 11.2.0

  3. I am using it on
    * Application Express Instance URL : http://localhost::8181/ords
    * Application Express Images URL : http://localhost:8181/i/
    * SMTP Host Address : localhost
    * SMTP Host Port : 25
    * SMTP Authentication Username :
    * SMTP Authentication Password :
    * Use SSL/TLS : No
    * Default Email From Address :
    * Maximum Emails per Workspac : 1000
    Error : Manage Instance -> Mail Queue
    ORA-29278: SMTP transient error: 421 Service not available

  4. ERROR at line 1:
    ORA-20001: This procedure must be invoked from within an ap
    ORA-06512: at “APEX_050000.WWV_FLOW_MAIL”, line 562
    ORA-06512: at “APEX_050000.WWV_FLOW_MAIL”, line 588
    ORA-06512: at “APEX_050000.WWV_FLOW_MAIL_API”, line 97

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.