In this tutorial i will show you how to send mail from Oracle APEX. Lets start….

This tutorial divide into tree parts-

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

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;

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……