Send mails using UTL_SMTP in Oracle

The UTL_SMTP Oracle package provides capability to send emails from PL/SQL program using Simple Mail Transfer Protocol. UTL_MAIL is easy to use as it eliminates steps of formatting the message body and explicit coding of details required by SMTP protocol.

Use below simple example to send mails using UTL_MAIL

begin
utl_mail.send(
sender => 'fromuser@oracleracexpert.com',
recipients => 'touser@oracleracexpert.com',
message => 'Test mail for UTL_MAIL'
);
end;

If you can want to use UTL_MAIL refer “Send EMAILs using UTL_MAIL”

UTL_SMTP it is more fine-grained API that enables the attachments of larger size and it is very powerful utility.

Use below simple example to send mails using UTL_SMTP

DECLARE
mailhost VARCHAR2(64) := ‘localsmtp.oracleracexpert.com;
sender VARCHAR2(64) := 'fromuser@oracleracexpert.com';
recipient VARCHAR2(64) := 'touser@ oracleracexpert.com';
smtp_mail_conn utl_smtp.connection;
BEGIN
mail_conn := utl_smtp.open_connection (mailhost, 25);
utl_smtp.helo (smtp_mail_conn, mailhost);
utl_smtp.mail (smtp_mail_conn, sender);
utl_smtp.rcpt (smtp_mail_conn, recipient);
utl_smtp.data (smtp_mail_conn, 'This is a test mail using UTL_SMTP '
chr(10));
utl_smtp.quit (smtp_mail_conn);
END;
/

You can write multiple line messages using the UTL_SMTP.WRITE_DTA command, use following example

CREATE OR REPLACE PROCEDURE send_mail_smtp(recipient IN VARCHAR2,
sender IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2,
mailhost IN VARCHAR2,
smpt_port IN NUMBER DEFAULT 25)
AS
smtp_mail_conn UTL_SMTP.connection;
BEGIN
Smtp_mail_conn := UTL_SMTP.open_connection(mailhost, smtp_port);
UTL_SMTP.helo(smtp_mail_conn, mailhost);
UTL_SMTP.mail(smtp_mail_conn, sender);
UTL_SMTP.rcpt(smtp_mail_conn, recipient);
UTL_SMTP.open_data(smtp_mail_conn);
UTL_SMTP.write_data(smtp_mail_conn, 'To: '||recipient||UTL_TCP.crlf);
UTL_SMTP.write_data(smtp_mail_conn, 'From: '||sender||UTL_TCP.crlf);
UTL_SMTP.write_data(smtp_mail_conn, 'Subject: '||subject||UTL_TCP.crlf);
UTL_SMTP.write_data(smtp_mail_conn, message||UTL_TCP.crlf||UTL_TCP.crlf);
UTL_SMTP.close_data(smtp_mail_conn);
UTL_SMTP.quit(smtp_mail_conn);
END;
/

Call the procedure by providing the below details

BEGIN
send_mail_smtp(recipient => 'touser@mycompany.com',
sender => 'fromuser@oracleracexpert.com',
subject => 'Test mail',
message => 'This is a test mail using UTL_SMTP.',
mailhost => localsmtp.oracleracexpert.com');
END;
/

To send emails to multiple recipients use UTL_SMTP.rcpt procedure for each separate email
For more information you can also refer Oracle Documentation http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_smtp.htm#ARPLS074

Common errors when using UTL_SMTP:-
1. The UTL_SMTP package requires the ORACLE xmldb component, please install the same to fix below error

ERROR at line 1:
ORA-24248: XML DB extensible security not installed
ORA-06512: at "SYS.UTL_TCP"

2. You may receive below error if the “mailhost” was not set correctly

ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 20

3. You will receive below error if ACL is not assigned to target host and necessary privileges are not granted

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 127
ORA-06512: at "SYS.UTL_SMTP", line 150
ORA-06512: at line 7