Sending Email Using Oracle Apex and PL/SQL With Examples

Objective:

Using Oracle Apex Package Apex mail, How we can frame mails and send with the help of oracle PL/SQL Program.

Definition:

We have 3 Procedures inside Apex_mail,

Send
Add_attachment
Push_queue

Syntax:

(1)APEX_MAIL.SEND (

p_to IN VARCHAR2,

p_from IN VARCHAR2,

p_body IN [ VARCHAR2 | CLOB ],

p_body_html IN [ VARCHAR2 | CLOB ] DEFAULT NULL,

p_subj IN VARCHAR2 DEFAULT NULL,

p_cc IN VARCHAR2 DEFAULT NULL,

p_bcc IN VARCHAR2 DEFAULT NULL,

p_replyto IN VARCHAR2);

(2)APEX_MAIL.ADD_ATTACHMENT (

p_mail_id IN NUMBER,

p_attachment IN BLOB,

p_filename IN VARCHAR2,

p_mime_type IN VARCHAR2);

(3)APEX_MAIL.PUSH_QUEUE;

Monitoring Table:

APEX_MAIL_LOG

APEX_MAIL_QUEUE

Configuration Setting for Using Apex_mail:

APEX Mail Server Setup:

The APEX mail system sends email to a mail server or mail relay. The details of the service need to be set at in the APEX instance. You can find that location here.

(1)Log into APEX using the INTERNAL workspace. Navigate to “Manage Instance > Instance Settings > Email (tab)”.

(2)Set your mail server details, then click the “Apply Changes” button.

Note: If you are using a local mail relay on the database server, the default settings of “localhost”

And port 25 should work fine.

Remember, if you are accessing an external mail server using TLS, you will need to include the root certificate in the wallet referenced by the APEX instance.

(1) Log into APEX using the INTERNAL workspace.

(2) Navigate to “Manage Instance > Instance Settings > Wallet (tab)”.

(3) Enter the path to the wallet, prefixed with “file:”. The wallet password is not needed if the wallet is set to auto-login. Click the “Apply Changes” button.

Network ACL:

We need to make sure the . database can make a callout to the mail server. This requires a network ACL for the specific host and port.
In the following example we are using “localhost:25”, a local relay on the database server. The principal of the ACL must be “APEX_XXXXXX” user.

declare

l_username varchar2(30) := ‘APEX_210100’;

begin

dbms_network_acl_admin.append_host_ace(

host => ‘localhost’,

lower_port => 25,

ace => xs$ace_type(privilege_list => xs$name_list(‘connect’),

principal_name => l_username,

principal_type => xs_acl.ptype_db));

commit;

end;

Note:

If we want to use the APEX_MAIL package from PL/SQL without APEX itself, we need to manually set the security group to a valid workspace for our session.

begin

apex_util.set_workspace(p_workspace => ‘DEV_WS’);

end;

Examples:

Example 1: Simple Email

begin

apex_mail.send

(p_to=>’*******@GMAIL.COM’,

p_from=>’*******@GMAIL.COM’,

p_subj=>’Welcome Emailer’,

p_body=>’Welcome to Oracle Apex Sending Emailer Session’);

APEX_MAIL.PUSH_QUEUE;

end;

Output:

Note:

select * from apex_mail_queue;–Every 5 minutes once Mail queue Processed

In order to Force Send, we will use below procedure– APEX_MAIL.PUSH_QUEUE

Example 2: Email with html body

begin

apex_mail.send

(p_to=>’*******@GMAIL.COM’,

p_from=>’*******@GMAIL.COM’,

p_subj=>’Welcome Emailer’,

p_body=>’Welcome to Oracle Apex Sending Emailer Session’,

p_body_html=>'<table style=”width:100%”>

<tr>

<th>Sessio Name</th>

<th>Handled by</th>

<th>Timings</th>

</tr>

<tr>

<td>Email sending in Oracle Apex and Oracle PL/SQL</td>

<td>Aishwarya</td>

</tr>

</table>’);

APEX_MAIL.PUSH_QUEUE;

end;

Output:

Example 3: Email with Html table with styles

begin

apex_mail.send

(p_to=>’***********@gmail.com’,

p_from=>’**********@gmail.com’,

p_subj=>’Welcome Emailer’,

p_body=>’Welcome to Oracle Apex Sending Emailer Session’,

p_body_html=>'<html><head><style>

table {

font-family: arial, sans-serif;

border-collapse: collapse;

width: 100%;

}

td, th {

border: 1px solid #dddddd;

text-align: left;

padding: 8px;

}

tr:nth-child(even) {

background-color: #dddddd;

}

</style>

</head>

<body>

<h2>SESSION Details</h2>

<table>

<tr>

<th>Sessio Name</th>

<th>Handled by</th>

<th>Timings</th>

</tr>

<tr>

<td>Email sending in Oracle Apex and Oracle PL/SQL</td>

<td>Aishwarya</td>

</tr>

</table>

</body>

</html>

‘);

APEX_MAIL.PUSH_QUEUE;

end;

Output:

Example 4: character limitation and steps to overcome. And Adding Signature line to the Email.

declare

l_body varchar2(2000):=’Welcome to Oracle Apex Sending Emailer Session’ ;

l_body_html varchar2(4000);

begin

l_body_html:='<html>

<head>

<style>

table {

font-family: arial, sans-serif;

border-collapse: collapse;

width: 100%;

}

td, th {

border: 1px solid #dddddd;

text-align: left;

padding: 8px;

}

tr:nth-child(even) {

background-color: #dddddd;

}

</style>

</head>

<body>

<h2>SESSION Details</h2>

<table>

<tr>

<th>Sessio Name</th>

<th>Handled by</th>

<th>Timings</th>

</tr>

<tr>

<td>Email sending in Oracle Apex and Oracle PL/SQL</td>

<td>Aishwarya</td>

</tr>

</table>

</body>

</html>’||utl_tcp.crlf;

l_body_html := l_body_html ||'<p>Note:Do Attend and Show your Interest towards <strong>Our Session.</strong></p>’||utl_tcp.crlf;

l_body_html := l_body_html ||’Sincerely,<br />’||utl_tcp.crlf;

l_body_html := l_body_html ||’ <span class=”sig”>Custom Development Team</span><br />’||utl_tcp.crlf;

apex_mail.send

(p_to=>’*******@gmail.com’,

p_from=>’********@gmail.com’,

p_subj=>’Welcome Emailer’,

p_body=>l_body,

p_body_html=>l_body_html);

APEX_MAIL.PUSH_QUEUE;

end;

Ouput:

Example 5: Sending attachment using apex_mail

Blob file as attachment:

DECLARE

l_id NUMBER;

BEGIN

l_id := APEX_MAIL.SEND(

p_to => ‘xxxxx@gmail.com’ ,

p_from => ‘xxxxx@gmail.com’ ,

p_body => ‘Welocme to Oracle Apex Sending Emailer Session’ ,

p_body_html => ‘<html><head><style>

table {

font-family: arial, sans-serif;

border-collapse: collapse;

width: 100%;

}

td, th {

border: 1px solid #dddddd;

text-align: left;

padding: 8px;

}

tr:nth-child(even) {

background-color: #dddddd;

}

</style>

</head>

<body>

<h2>SESSION Details</h2>

<table>

<tr>

<th>Sessio Name</th>

<th>Handled by</th>

<th>Timings</th>

</tr>

<tr>

<td>Email sending in Oracle Apex and Oracle PL/SQL</td>

<td>Aishwarya</td>

</tr>

</table>

</body>

</html>

‘ ,

p_subj =>’Welcome Emailer’);

FOR c1 IN (SELECT filename, blob_content, mime_type

FROM apex_application_files

WHERE ID IN (select max(id) from

apex_application_files))

LOOP

APEX_MAIL.ADD_ATTACHMENT(

p_mail_id => l_id,

p_attachment => c1.blob_content,

p_filename => c1.filename,

p_mime_type => c1.mime_type);

END LOOP;

COMMIT;

END;

Output:

Example 6: Sending Multiple File as Attachment:

DECLARE

l_id NUMBER;

BEGIN

l_id := APEX_MAIL.SEND(

p_to => ‘xxxxxxxx@gmail.com’ ,

p_from => ‘xxxxxxxx@gmail.com’ ,

p_body => ‘Welocme to Oracle Apex Sending Emailer Session’ ,

p_body_html => ‘<html><head><style>

table {

font-family: arial, sans-serif;

border-collapse: collapse;

width: 100%;

}

td, th {

border: 1px solid #dddddd;

text-align: left;

padding: 8px;

}

tr:nth-child(even) {

background-color: #dddddd;

}

</style>

</head>

<body>

<h2>SESSION Details</h2>

<table>

<tr>

<th>Sessio Name</th>

<th>Handled by</th>

<th>Timings</th>

</tr>

<tr>

<td>Email sending in Oracle Apex and Oracle PL/SQL</td>

<td>Aishwarya</td>

</tr>

</table>

</body>

</html>

‘ ,

p_subj =>’Welcome Emailer’ );

FOR c1 IN (SELECT filename, blob_content, mime_type

FROM apex_application_files

WHERE ID IN (11918898404194145282,12992585427001103947))

LOOP

APEX_MAIL.ADD_ATTACHMENT(

p_mail_id => l_id,

p_attachment => c1.blob_content,

p_filename => c1.filename,

p_mime_type => c1.mime_type);

END LOOP;

COMMIT;

END;

Output:

Example 7: Table Data as Email Body

DECLARE

l_body CLOB := NULL;

CURSOR c1 IS

SELECT a.empno,a.ename,a.job,a.sal,b.DNAME

FROM emp a,

dept b

where a.deptno=b.DEPTNO

ORDER BY a.EMPNO;

BEGIN

l_body := l_body

|| ‘<table><tr><th>Employee Details</th></tr><tr>

<th><b>EMPNO</b></th>

<th>ENAME</th>

<th>Department Name</th> </tr>’;

FOR rec IN c1 LOOP

l_body := l_body || ‘<tr> <td>’ || rec.EMPNO

||'</td> <td>’ || rec.ENAME

||'</td> <td>’ || rec.JOB

||'</td> <td>’ || rec.SAL

||'</td> <td>’ || rec.DNAME

||'</td> </tr>’|| utl_tcp.crlf;

END LOOP;

l_body := l_body || ‘</table>’ || utl_tcp.crlf;

l_body := l_body || ‘<br>’ || utl_tcp.crlf;

l_body := l_body || ‘</td>’|| utl_tcp.crlf;

l_body := l_body || ‘</tr>’|| utl_tcp.crlf;

l_body := l_body || ‘</table>’|| utl_tcp.crlf;

–dbms_output.Put_line(l_body);

apex_mail.Send(p_to => ‘xxxxxxxx@gmail.com’,

p_from => ‘xxxxxxxx@gmail.com’,

p_subj => ‘Employee Details’,

p_body => l_body,

p_body_html => l_body);

apex_mail.push_queue;

END;

Output:

Note:

There are more use cases with respect to “Apex_mail” .Here, i have covered some of the basic examples.

Aishwarya Rajagopal