I was looking for some documentation in order to implement a function that can send an email with attachments from a PL/SQL stored procedure.
Was kind of difficult to find very good documentation although this is a very simple process, specially about how to implement the function of sending some text as an attached file.
The are 2 main approaches to solve this problem:
1. UTL_SMTP: Available in Oracle 8i and 9i (for 10g use UTL_MAIL), is not easy to use to send emails with attached files but if you understand the concept of how the SMTP server handles this will be much easier. I will cover this below.
2. Using Java, easier to send attachments but difficult to install and setup (you got to have access to user SYS in order to compile and run some functions in Java and also to run some commands to be able to compile the Java procedure)
In this article I will refer to the first approach, using UTL_STMP. I will save the Java approach for a later post in this blog.
How does SMTP work to send attachments?What the SMTP server does in order to send an email with a text file attached it's to get the text that arrives in one of the parameters or arguments passed to the function. The SMTP receives the text and encodes or decodes the text on the fly, depending if it's sending or receiving the email. So when it arrives to your computer it's when it gets 'translated' into a certain type of file.
This is the way that the process of sending a TXT attachments works. The SMTP server uses some commands to send the text and not the file itself. This means that contents when arrive to the destination will be stored as an attached file.
This the reason why when sending an attached file it's not needed to specify a path. You just pass the contents which will be encoded or decoded on the fly when receiving or sending the email...
I don't know why is that NOBODY explains this simple but very useful concept. All the sites that I have seen only bother to put the code of the functions needed to send email with attachments but nobody explains anything, the 'know-how' it's kept as a secret.
Now that you know how SMTP works let's get into action using PL/SQL
Using UTL_SMTP to send mails with attachmentsUTL_SMTP, as the name itself referes, it's a UTiLity Package included by default in Oracle Database since 8i release. It has some built in functions and procedures that basically provide an interface with the SMTP protocol commands. So the idea behind it's to interact with the SMTP server initiating and mantaining a conversation to achieve the functionality of sending emails from any stored procedure defined in PL/SQL
To implement this functionality inside your PL/SQL procedures, some functions have to be called; there is no need to initialize/setup the package because it comes pre-installed in the database server. Check the functions used, the parameters are self explanatory and the variables have to be declared previously.
Note that to send the attachment there is no need to call an specific function, the action of initializing some parameters, like the file name that will contain the attached text when the email it reaches its destination, is the only thing needed by the STMP server to send an email including text as an attachment.
-- initiate the conversation with the SMTP server, establishing the connection v_smtp_connection := utl_smtp.open_connection( v_smtp_host, v_smtp_port );
-- perform a handshake with the SMTP server utl_smtp.helo( v_smtp_connection, v_smtp_host );
-- sender (from) address v_sender:= user@server.com
-- set the 'from' address of the message utl_smtp.mail( v_smtp_connection, v_sender );
-- recipient (to) address
v_sender:= user@host.com
-- set the 'to' address of the messageutl_smtp.rcpt(v_smtp_connection, v_recipient);
-- send the email, using char(13) it's a way to pass the parameters to the SMTP serverutl_smtp.data(v_smtp_connection,'Single string message.' || Chr(13));
-- close the connection UTL_SMTP.quit(l_mail_conn);
Sample CodeThis code it's to demonstrate the use of the UTL_SMTP package in a real life piece of working code that I use on a application implemented for a datamart application in an actual project that I am working on. Look that the text to be attached comes as a parameter to the function, it can hold up to 32768 characters long, if you need you can use a CLOB datatype which can handle up to 4 Gigs. There is also a place to name the file name that will hold the attached text once it reaches its destination.
The values To, Cc, Bcc are only used to display the recicpients of the email in a certain way in the email client (Hotmail, Eudora, Outlook, Yahoo, etc) . The values on this fields have nothing to do actually with the mechanism that sends the email as this fields are used only for representation purposes of how the recipients will be shown in each email client who will be used to read the email.
This function is commonly used in applications to send results from queries and it's usually used inside a for cursor loop.
Function send_mail(v_recipient varchar2, -- Field To of the email
v_subject varchar2, -- Subject of the email
v_body varchar2, -- Body of the email
v_attachment varchar2, -- Text that will be sent as an attached file
v_error_msg out varchar2) -- To hold the errors in the exception section
return boolean is
-- variable to hold the smtp server connection
v_smtp_connection utl_smtp.connection;
-- variable to hold the smtp host name
v_smtp_host varchar2(100) default 'my_smtp.com';
-- variable to hold the smtp port
v_smtp_port number default 25;
-- variable to hold the sender, from field
v_sender varchar2(100) default 'user@host.com';
begin
-- establish the connection to the smtp server
v_smtp_connection := utl_smtp.open_connection(v_smtp_host, v_smtp_port);
-- perform a handshake with the smtp server
utl_smtp.helo(v_smtp_connection, v_smtp_host);
-- set the 'from' address of the message
utl_smtp.mail(v_smtp_connection, v_sender);
-- add the recipient to the message
utl_smtp.rcpt(v_smtp_connection, v_recipient);
-- send the email
utl_smtp.data(v_smtp_connection,
'Date: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || utl_tcp.crlf ||
'From: ' || v_sender || utl_tcp.crlf ||
'Subject: '|| v_subject || utl_tcp.crlf ||
'To: ' || v_recipient || utl_tcp.crlf ||
'Cc: ' || v_cc || utl_tcp.crlf ||
'MIME-Version: 1.0'|| utl_tcp.crlf || -- use Mime mail standard
'Content-Type: multipart/mixed;' || utl_tcp.crlf ||
' boundary="-----SECBOUND"'|| utl_tcp.crlf ||
utl_tcp.crlf ||
'-------SECBOUND'|| utl_tcp.crlf ||
'Content-Type: text/html;'|| utl_tcp.crlf ||
'Content-Transfer_Encoding: 8bit'|| utl_tcp.crlf ||
utl_tcp.crlf ||
v_body || utl_tcp.crlf || -- message body
utl_tcp.crlf ||
'-------SECBOUND'|| utl_tcp.crlf ||
'Content-Type: text/plain;' || utl_tcp.crlf ||
' name="error.log"'|| utl_tcp.crlf || -- file name that will hold the attached text
'Content-Transfer_Encoding: 8bit'|| utl_tcp.crlf ||
'Content-Disposition: attachment;'|| utl_tcp.crlf ||
' filename="error.log"'|| utl_tcp.crlf ||
utl_tcp.crlf ||
v_attachment || utl_tcp.crlf || -- attachment
utl_tcp.crlf ||
'-------SECBOUND--' -- end mime mail
);
-- end the connection to the smtp server
utl_smtp.quit(v_smtp_connection);
return true;
exception
when utl_smtp.invalid_operation then
v_error_msg := ' Invalid Operation in Mail attempt using UTL_SMTP.';
return false;
when utl_smtp.transient_error then
v_error_msg := ' Temporary e-mail issue - try again';
return false;
when utl_smtp.permanent_error then
v_error_msg := ' Permanent Error Encountered.';
return false;
end send_mail;
If you need any clarifications or you have found some errors in the source code please send me an email to: gongorac@gmail.com or drop me a note below.
