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

66 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

  5. Have you ever considered writing an e-book or guest authoring on other sites?
    I have a blog centered on the same ideas you discuss and would really like to have you share some stories/information. I
    know my viewers would enjoy your work. If you’re even remotely interested, feel free to send me an e-mail.

  6. After going over a handful of the blog articles on your site, I honestly like your way of writing a blog.
    I saved as a favorite it to my bookmark site list and will be checking back soon. Please check out my website as well and
    let me know how you feel.

  7. It’s the best time to make some plans for the future and it’s time
    to be happy. I’ve read this post and if I could I want to suggest
    you few interesting things or suggestions. Perhaps you can write next articles referring to this
    article. I desire to read more things about it!

  8. I was suggested this web site by my cousin. I’m not sure whether this
    post is written by him as no one else know such detailed about my problem.
    You are amazing! Thanks!

  9. Ніǃ
    Ι aрplaud women who havе the cоurаge tо enjоу thе lоvе of mаny wоmen and сhоoѕe the оnе who will bе her best frіеnd during thе bumpy аnd crаzу thing called lіfe.
    Ι wаnted to be that frіеnd, not ϳust thе stablе, rеliablе, bоrіng housewife of an ordіnаry marrіed соuрle.
    I am 22 уeаrs оld, Νorа, from Denmark.
    Μy profile iѕ here: http://igarcitnemicent.tk/zpg-8436/

  10. Good day I am so excited I found your website, I really
    found you by accident, while I was browsing on Google for something else, Anyhow I am here
    now and would just like to say many thanks for a marvelous post and
    a all round exciting blog (I also love the theme/design), I don’t have time to look
    over it all at the moment but I have bookmarked it and also added in your RSS feeds, so when I have
    time I will be back to read much more, Please do keep up the excellent work.

    Feel free to surf to my blog – fx마진거래 (Viola)

Leave a Reply

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