UTL_SMTP API to send HTML Emails from PL/SQL

Oracle has provided an API UTL_SMTP which can be used to send email from PL/SQL. UTL_SMTP uses SMTP (Simple Mail Transfer Protocol) sever to send the emails. To know more about UTL_SMTP API read oracle documentation here.

Below is a sample code snippet to send email using UTL_SMTP API.

DECLARE
--
v_from VARCHAR2(80)  := 'noreply@OracleAppsDNA.com';
v_recipient VARCHAR2(180) := 'shailender.thallam@xyz.com';
v_recipient2 VARCHAR2(180) := 'shailender@gmail.com';
v_subject VARCHAR2(180) := 'Test Email using UTL_SMTP';
v_mail_host VARCHAR2(80):= 'localhost'; --SMTP host name
v_mail_conn utl_smtp.connection;
crlf VARCHAR2(2) := chr(13)||chr(10); --can be replaced by utl_tcp.crlf
v_body VARCHAR2(10000);
v_msg varchar2(1222) := '


Test Message from www.OracleAppsDNA.com
This Test Email is sent using UTL_SMTP API
';
--
BEGIN
--
v_mail_conn := utl_smtp.open_connection(v_mail_host, 25); --25 is SMTP Port Number
utl_smtp.helo(v_mail_conn, v_mail_host);
utl_smtp.mail(v_mail_conn, v_from);
utl_smtp.rcpt(v_mail_conn, v_recipient);
utl_smtp.rcpt(v_mail_conn, v_recipient2);
--
v_body :=  'Date: ' || TO_CHAR(sysdate, 'dd Mon yy hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '||v_Subject || crlf ||
'To: ' || v_Recipient || crlf ||
'CC: ' || v_Recipient2 || crlf ||v_msg|| '';
--
utl_smtp.DATA (v_mail_conn, 'MIME-Version: 1.0' || crlf || 'Content-type: text/html' || crlf || v_body );
utl_smtp.quit(v_mail_conn);
--
EXCEPTION WHEN OTHERS
THEN
--
dbms_output.put_line(SQLCODE ||' - '||SQLERRM);
--
END;

Now lets get into some inner details of the above code snippet.

SMTP Host Name

v_mail_host VARCHAR2(80):= 'localhost'; --SMTP host name

SMTP host name is the name of the email server which uses SMTP (Simple Mail Transfer Protocol) protocol to handle all outgoing e-mail messages. SMTP protocol uses port number 25 to handle out going emails, this is given as input to utl_smtp.open_connection as shown below.

v_mail_conn := utl_smtp.open_connection(v_mail_host, 25);

How to find SMTP host name?
Execute the following query to findout the SMTP host name of your instance.

 SELECT fscpv.parameter_value,
  fscpt.display_name         ,
  fscpt.description
   FROM fnd_svc_comp_params_tl fscpt,
  fnd_svc_comp_param_vals fscpv
  WHERE 1              = 1
AND fscpt.display_name = 'Outbound Server Name'
AND fscpt.language     = 'US'
AND fscpt.source_lang  = 'US'
AND fscpt.parameter_id = fscpv.parameter_id;

CRLF

crlf VARCHAR2(2) := chr(13)||chr(10); --can be replaced by utl_tcp.crlf

chr(13) + chr(10) = new line character.

chr(13) = Carriage Return (moves cursor to leftmost side)
chr(10) = New Line (drops cursor down one line)

So the combination of chr(13) and chr(10) is to create a line break within a string.
In short its just like ‘dbms_output.put_line’.

SMTP Commands

utl_smtp.helo, utl_smtp.mail, utl_smtp.rcpt, etc. are the procedures which call SMTP commands, below are the list of standard SMTP commands and their description.

• HELO – introduce yourself
• EHLO – introduce yourself and request extended mode
• MAIL – specify the sender
• RCPT – specify the recipient
• DATA – specify the body of the message (To, From and Subject should be the first three lines.)
• RSET – reset
• QUIT – quit the session
• HELP – get help on commands
• VRFY – verify an address
• EXPN – expand an address
• VERB – verbose

Note:
The above script is for sending HTML emails which is achieved by the following code

utl_smtp.DATA (v_mail_conn, 'MIME-Version: 1.0' || crlf || 'Content-type: text/html' || crlf || v_body );