How to send emails with attachments from PL/SQL stored procedures using Java

I was looking for some documentation in order to implement a function that can send an email with attachments from an Oracle PL/SQL stored procedure. It was kind of difficult to find very good documentation, hence this is a simple process, specially about how to implement the function to send attached files.

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 simpler.

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 second approach, using Java which is pending since I wrote a post about the first approach on August/08, which you can check in this blog itself, here.

Using the JavaMail API

The first thing to check is that you must have a Java enabled Oracle database (8i or later). If that is the case you can download two set of files that are needed to implement this solution. The JavaMail API and Java Activation Framework (JAF) are needed.

You can get the JavaMail API objects pressing here.

The download you get will consist of several hundred files; only one of which we are interested in as explained below. After downloading the JavaMail API make sure also to get the JavaBeansTM Activation Framework extension or JAF (javax.activation). This is needed to run the JavaMail API package.

You can download the JAF from here.

After you have downloaded these two sets of files you will need to decompress them and extract two files:

• Mail.jar from the JavaMail APIdownload
• Activation.jar from the JAF download

This is all you will need for this implementation. These two files extracted may have a different name, so this has to be changed accordingly in the commands below in order to loaded them properly.

The two jar files have to be loaded to the database using SYS user since they have 'protected' Java packages that regular users cannot upload. Loadjava.bat utility has to be used (this utility is found under BIN directory in your Oracle installation path).

These are the commands and the arguments are full explained below:

• loadjava -u sys/manager -o -r -v -f -noverify -synonym -g public mail.jar
• loadjava -u sys/manager -o -r -v -f -noverify -synonym -g public activation.jar

Where:

· -u sys/manager: is the userid and password for SYS account. Some of the packages are protected and must be loaded as SYS

· -o: is shorthand for oci8 driver

· -r: is short for resolve, this will resolve all external references in the loaded classes helping to verify that the loaded java classes will be able to function after loading them

· -v: is short for verbose, used to see the output through each step of the process

· -f: is short for force, this isn't necessary on the first load but is OK to use. If loadjava hits an error can be corrected and reloaded, for this is needed to use the dropjava command to drop the jar file from the database or use force, using force makes it easier

· -noverify: does not attempt to verify the bytecode. You must be granted oracle.aurora.security.JServerPermissio(Verifier) to execute this option. In addition, this option must be used in conjunction with –r, SYS has this privilege. This is needed because the bytecode verifier will flag some issues with the mail.jar file and this works around that issue

· -synonym: creates public synonyms for the classes, since the mail java code will be installed as different user than SYS this will allow to 'see' the SYS loaded java classes under that user

· -g public: grants execute on these loaded classes to PUBLIC. If this is not desirable, needs to be changed to be just the user who will create the 'send mail' routines, for example -g MY_USER

2. Create and compile the Java stored procedure running the script provided named email_java.sql, get it from here. Use SQL Plus and connect with the user who will own the code, can be any, for this implementation we will call it MY_USER. Before running this script with SQL Plus use 'set define off' and if there are errors running the script you can see them using 'show err'.

3. Create an compile the PL/SQL function Send, which can be downloaded from here. This function will be used in the next step and it's just a wrapper to map PL/SQL data types to Java data types. Use SQL Plus and connect with MY_USER

4. Create an compile the PL/SQL procedure Test_email, which can be downloaded from here. Use SQL Plus to do this and connect with MY_USER. This procedure will call the function created in Step 3 and will contain all you need in order to start sendig emails. Below you can see the main section of the code.

I use a table called documents which has a BLOB data type column called document_content whose contents have zipped files. You will have to implement a way to pass the files from a similar table in your database. If you don't know who to store BLOB files in the database this is a good time you can work on a little bit to learn how to implement this operation. You can also check this blog later because I will write another post about this subject indeed.

Here is the main section of the code you will find in test_email procedure, which is self explanatory:


for cur in (select document_content from documents ) loop

v_ret_code := send(
p_from => 'user@host.com',
p_to => 'user@host.com',
p_cc => NULL,
p_bcc => NULL,
p_subject => 'Use the attached Zip file',
p_body => 'to send email with attachments...',
p_smtp_host => 'email.host.com', -- your SMTP server/host
p_attachment_data => cur.document_content,
p_attachment_type => 'application/winzip',
p_attachment_file_name => 'filename.zip');

if v_ret_code = 1 then
dbms_output.put_line ('Successfully sent message...');
else
dbms_output.put_line ('Failed to send message...');
end if;

end loop;


5. Finally you have to grant Java privileges to MY_USER (to resolve and set SMTP host), using dbms_java.grant_permission()

* dbms_java.grant_permission( grantee => 'MY_USER', permission_type => 'java.util.PropertyPermission', permission_name => '*', permission_action => 'read,write' );

* dbms_java.grant_permission( grantee => 'MY_USER', permission_type => 'java.net.SocketPermission', permission_name => '*', permission_action => 'connect,resolve' );

This has to be done by user SYS. Please beware this two notes:

5.1. In the grant on java.net.SocketPermission, the string SMTP_SERVER_NAME has to be replaced by the real server name. This restricts the access permissions to use the Java code only to that server or host.

5.2. The other permission, java.util.PropertyPermission, is needed in order to set the mail.smtp.host in the session properties when we are calling the method props.put() in the Java class.

That's it. If you have any doubt just drop me a note at gongorac@gmail.com

Enjoy it!!