UTL_MAIL with SMTP authentication

Details

If you are using UTL_MAIL you will soon come to some limits of this package.

  •  it does not support SMTP authentication
  •  it does not support attachments (not provided here)

Last week I needed the authentication part for a project.
I decided to take something that's already stable ready to be used, and free.
It's Apache Commons Mail part. Basically these class makes handling in Java much easier than provided by SUN/ORACLE.
es, it's a Java-class. But Java is really easy to use in Oracle, you will see.

Basically you get the jar, say "loadjava myjar.jar", create a simple java-class in oracle, create a simple pl/sql wrapper.
This cooking recipe is on Linux, but except the unpacking it's almost the same on windows.
And don't forget to take the most current and stable jar-files!

get the mail.jar from here http://www.oracle.com/technetwork/java/javamail/index.html

get the commons-email-xxx.jar from here http://commons.apache.org/email/

unzip both in a directory on your database server and use following statements to load it into the database (REPLACE USER/PW!!!):

 

1
2
3

loadjava -u myuser/MYPW -resolve mail.jar
loadjava -u myuser/MYPW -resolve commons-email-xxx.jar
 

 

Now we need some Java and PL/SQL programming. I did the design so that it can be basically used instead of UTL_MAIL, means same parameter names and default-values.

Just some more parameters were added for using the authentication.
You can download the files from the download-sections here. Just install them with SQL/PLUS or whatever tool you like.

After compiling the programs into the databse give it a try. You need to set some permissions, so keep DBMS-Output enabled, in the output you find the statements that needs to be executed. It will look like similar to this:

1
2
3

dbms_java.grant_permission( 'yippie', 'SYS:java.lang.RuntimePermission', 'accessDeclaredMembers', '' );
dbms_java.grant_permission( 'XYA', 'SYS:java.util.PropertyPermission', '*', 'read,write' );
begin dbms_java.grant_permission( 'XYA', 'SYS:java.net.SocketPermission', '123...', 'connect,resolve' );

 

Ok for testing we need a testscript:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14

BEGIN

dbms_output.enable(10000);
dbms_java.set_output(1000000);
std_mail.send(
   sender => This email address is being protected from spambots. You need JavaScript enabled to view it.',
   recipients => This email address is being protected from spambots. You need JavaScript enabled to view it.',
   subject => 'subjective',
   message => 'messagemessagemessage',
   smtphostname => 'mail.mytestdomain.com',
   smtpport => '587',
   smtpuser => 'myuser',
   smptpassword => 'mypw',
   useTls => false);
END;
/

That's it!

The programs here are released under the GNU/GPL2.

Have fun!

Feedback appreciated, please comment!