Enable Oracle database to send emails via SMTP server

Often in a complex enterprise Infrastructure Oracle DBAs face issues by enabling SMTP mail traffic on the databases through corporate email gateway servers. Imagine you have to provide your database applications an ability to send emails via Simple Mail Transfer Protocol (SMTP) protocol from Oracle database. Below I give a detail action plan to accomplish the same. My test example includes an Oracle database 11gR2 running on Linux RedHat 6 and a Microsoft Exchange corporate server.

1. Oracle packages SYS.UTL_SMTP and SYS.UTL_TCP

Check if Oracle packages SYS.UTL_SMTP and SYS.UTL_TCP are available on Oracle database and you have EXECUTE grants on them.

2. Check SMTP access of database Linux server on mail server

– Check whether you are able to contact the email gateway server via SMTP from the database Linux box:

$ telnet smtp_server 25

If you see blank screen or an error: “telnet: Unable to connect to remote host: Connection refused”,
your DB server is not recognized by the SMTP server. In this case you have to apply for mail SMTP access.
Otherwise type the following commands to test sending email from Linux to your corporate email account:

helo

mail from: my_email@my_company.com # you should see "Sender OK'

rcpt to: my_email@my_company.com   # you should see "Recipient OK"

data                               # Start mail input

test email via SMTP and orcl DB

[Enter]

.                                  # mail should be sent

[Enter]

quit

3. Apply for mail SMTP access

Contact your mail (exchange) admins and apply for SMTP access on your corporate smtp mail gateway server. Below is an example:
Please allow SMTP traffic at smtpgate.mycompany.com for the following new server:
Hostname: ..com
IP: 99.999.99.99
Function: Oracle production DB …
Outgoing email: my_app@my_company.com
Amount emails per month: around 50-100
After that your mail admins will add a sender IP address to a corporate SMTP mail server to allow the sender Linux server access the SMTP server. This will enable the SMTP server sending emails on behalf of the sender (your Oracle database Linux box).
Note that the mail server can check if the sender email exists. If not, you can send an email from a dummy email account.

4. Sending email from Oracle database via SMTP

-- Place twice in the below procedure your actual smtp_server name

create or replace procedure testmail

(fromm varchar2,too varchar2,sub varchar2,body varchar2,port number)

is

objConnection utl_smtp.connection;

vrData varchar2(32000);

BEGIN

objConnection := UTL_smtp.open_connection('<smtp_server>',port);

UTL_smtp.helo(objConnection, '<smtp_server>');

UTL_smtp.mail(objConnection, fromm);

UTL_smtp.rcpt(objConnection, too);

UTL_smtp.open_data(objConnection);

/* ** Sending the header information */

UTL_smtp.write_data(objConnection, 'From: '||fromm || UTL_tcp.CRLF);

UTL_smtp.write_data(objConnection, 'To: '||too || UTL_tcp.CRLF);

UTL_smtp.write_data(objConnection, 'Subject: ' || sub || UTL_tcp.CRLF);

UTL_smtp.write_data(objConnection, 'MIME-Version: ' || '1.0' || UTL_tcp.CRLF);

UTL_smtp.write_data(objConnection, 'Content-Type: ' || 'text/html;');

UTL_smtp.write_data(objConnection, 'Content-Transfer-Encoding: ' || '"8Bit"' ||

UTL_tcp.CRLF);

UTL_smtp.write_data(objConnection,UTL_tcp.CRLF);

UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||'');UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||'');

UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||'<span style="color: red; font-family: Courier New;">'||body||'</span>');

UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||'');UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||'');

UTL_smtp.close_data(objConnection);

UTL_smtp.quit(objConnection);

EXCEPTION

WHEN UTL_smtp.transient_error OR UTL_smtp.permanent_error THEN

UTL_smtp.quit(objConnection);

dbms_output.put_line(sqlerrm);

WHEN OTHERS THEN

UTL_smtp.quit(objConnection);

dbms_output.put_line(sqlerrm);

END testmail;

/

-- this should send an email from a dummy account to your email address

execute testmail ('dummy@my_company.com','reciever_email@my_company.com','test Subject','Test Body Text',25);

– In case of following ORA- error go to step 2 and 3:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at “SYS.UTL_SMTP”, line 21
ORA-06512: at “SYS.UTL_SMTP”, line 97
ORA-06512: at “SYS.UTL_SMTP”, line 139
The “ORA-29278: SMTP transient error: 421 Service not available” error indicates that the problem
is not with the UTL_SMTP package but the fact that your database server’s network configuration does
not allow it to contact an external SMTP server.

– In case a following Oracle error, go to next step and enable ACL in Oracle 11g database:
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at “SYS.UTL_SMTP”, line 54
ORA-06512: at “SYS.UTL_SMTP”, line 138
ORA-06512: at “SYS.UTL_SMTP”, line 699
ORA-06512: at “me.TESTMAIL”, line 35
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1

5. Enable access control list (ACL) for your network hosts on 11g+

The network utility family of PL/SQL packages, such as UTL_TCP, UTL_INADDR, UTL_HTTP, UTL_SMTP, and UTL_MAIL, allow Oracle users to make network callouts from the database using raw TCP or using higher-level protocols built on raw TCP. As of Oracle 11g a new package DBMS_NETWORK_ACL_ADMIN allows fine-grained control over network hosts access using access control lists (ACL) implemented by XML DB. Below I show major steps that are required to enable ACL for user SCOTT. You have to run them as SYS or SYSDBA user. Read Oracle documentation for more details.

Create an ACL with one database user in Oracle database

BEGIN

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (

acl => 'smtp-gate-permissions.xml',

description => 'Permissions for smtp gate',

principal => 'SCOTT',

is_grant => TRUE,

privilege => 'connect'

);

COMMIT;

END;

/

Assign an ACL to one or more network hosts

BEGIN

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (

acl => 'smtp-gate-permissions.xml',

host => '<smtp_server>',

lower_port => 25,

upper_port => null);

COMMIT;

END;

/

Confirm the created ACL is correct

SELECT host, lower_port, upper_port, acl

FROM   dba_network_acls

/

SELECT acl,

principal,

privilege,

is_grant,

TO_CHAR(start_date, 'DD-MON-YYYY HH24:MI') AS start_date,

TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date

FROM   dba_network_acl_privileges

/

So that was my action plan for providing your applications an ability to send email via SMTP protocol from an Oracle database Linux server using a real example with UTL_SMTP package including ORA- error handling and ACL creation on Oracle 11g.

ORACLE DATABASE ADMINISTRATOR

Search

Thursday, June 23, 2016