Send query result from Apex 5.0 application as HTML Email

Recently I had a requirement to send a HTML based email from my apex application. The HTML content was actually the result of an sql query I run on my application database.

I used UTL_SMTP with database stored procedure to send mails in a plain text format already in my application.New requirement to send HTML based emails from UTL_SMTP looked bit complex and more coding effort. This can be easily achievable using APEX_MAIL.

To use APEX_MAIL package we have to configure Apex environment and database

http://docs.oracle.com/cd/E59726_01/doc.50/e39151/adm_wrkspc002.htm#AEADM29176

1. Enabling Network Services

If you are running Oracle Application Express with Oracle Database 11g or later, you must enable outbound mail. Starting with Oracle Database 11g Release 1 (11.1), the ability to interact with network services is disabled by default.

By default, the ability to interact with network services is disabled in Oracle Database 11g or later. Therefore, if you are running Oracle Application Express with Oracle Database 11g or later, you must use the DBMS_NETWORK_ACL_ADMIN package to grant connect privileges to any host for the APEX_050000 database user.

BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => ‘acl_for_apex_user.xml’,
description => ‘ACL for apex_user’,
principal => ‘APEX_050000’,
is_grant => TRUE,
privilege => ‘connect’,
start_date => SYSTIMESTAMP,
end_date => NULL);

COMMIT;
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => ‘acl_for_utl_smtp.xml’,
host => ‘smtp-server-host’,
lower_port => port,
upper_port => NULL);

COMMIT;

exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => ‘acl_for_apex_user.xml’,principal => ‘APEX_050000’,is_grant => true, privilege => ‘connect’);

2. Configure Apex Instance

Now, you have to add smtp server details on Apex Administration ‘Instance Settings’ section.

Sign in to Oracle Application Express Administration Services.
Click Manage Instance.

Under Instance Settings, click Instance Settings.

Under Email, enter the following:

SMTP Host Address
SMTP Host Port

Also, you can enter other details such as authentication user, Use SSL/TLS depending on your SMTP configuration

Now, in your application if you want to send a mail at the event of a button click, you can define a Dynamic Action and make it process during the button click

Please check my post for the PL/SQL code you can use for the Dynamic Action

Advertisements