IndahnyaBerbagi

Purpose
-------

This article shows how to send email using Java Stored Procedures (JSP)
and the Sun Microsystems JavaMail package. Attachments can also be sent.

****THIS NOTE IS ONLY SUPPORTED AGAINST AN 8I DATABASE AND NOT SUPPORTED
WHEN RUNNING WITHIN A 9I OR 10G DATABASE because the J2EE features
were deprecated with 9i so Java Mail went away. ****


Caution
-------

The sample program in this article is provided for educational purposes only
and is NOT supported by Oracle Support Services. It has been tested
internally, however, and works as documented. We do not guarantee that it
will work for you, so be sure to test it in your environment before relying
on it.


Scope & Application
-------------------

This article can be used as a guideline by any programmer who needs to
implement the functionality of sending e-mail from the Oracle 8i database.

****THIS NOTE IS ONLY SUPPORTED AGAINST AN 8I DATABASE AND NOT SUPPORTED
WHEN RUNNING WITHIN A 9I OR 10G DATABASE****

Introduction
------------

This article references JavaMail, a Java package provided by Sun Microsystems
that implements the mail functionality. For additional details, refer to:

http://java.sun.com/products/javamail

An interface to JavaMail is presented below in the form of a Java Stored
Procedure. Installing the Java class files using the loadjava utility is
also presented.


Advantages of JavaMail over mime++
----------------------------------

- No need for C++ compiler licenses
- Required Java class files are contained inside the database; no reliance on
external libraries
- Portability of JavaMail to any platform where Oracle 8i is available
- Easier to interface between PL/SQL and Java, than PL/SQL, C, and C++
- Everything can be compiled from one SQL*PLUS script
- JavaMail is free software provided by Sun Microsystems
- Robust and well-designed class suite
- Documentation available about JavaMail


Installation
------------

Download JavaMail (v1.1.3 or later) from http://java.sun.com/products/javamail
and the Javabeans(tm) Activation Framework. The archive consists of
documentation, demos, and a .jar file containing the required java classes.
Those .jar files must be extracted and loaded into the server using the
loadjava utility.

loadjava -user user/password -resolve -synonym activation.jar

loadjava -user user/password -resolve -synonym mail.jar

** Warning **
-------------------------------------------------------------------------

If you are loading classes into Oracle V816, you will most likely receive a
verifier warning during the loadjava process. The error appears as follows:

ora-29552: verification warning: at offset 12 of void (java.lang.String,
java.lang.String): cannot access class$java$io$InputStream
verifier is replacing byte code at void java.lang.String,
java.lang.String):12 by a throw
...
...

This is a Sun Microsystems bug that was uncovered when Oracle upgraded its
JDK from version 1.1 (Oracle V815) to 1.2.1 (Oracle V816).

This is only a warning: the classes will load. Our limited testing of this
mail package has not produced any runtime errors resulting from this bug,
however, you should test this thoroughly before relying on it.

-------------------------------------------------------------------------


************************************************************************
THIS NOTE IS ONLY SUPPORTED AGAINST AN 8I DATABASE AND NOT SUPPORTED
WHEN RUNNING WITHIN A 9I OR 10G DATABASE
************************************************************************


Once the classes have been loaded, you may need to resolve permission issues
using the following statements:

-- Grant needed permissions based on the Java 2 Security Standard

-- Allows access to system properties
exec dbms_java.grant_permission('SCOTT',
'java.util.PropertyPermission',
'*',
'read,write');

-- Allows access to sockets
exec dbms_java.grant_permission('SCOTT',
'java.net.SocketPermission',
'*',
'connect, resolve');

-- Allows access to local files
exec dbms_java.grant_permission('SCOTT',
'java.io.FilePermission',
'/dir/dir/attachments/*',
'read, write');

Next, the following SQL*PLUS script should be executed. It simply creates a
Java class named SendMail with only one member function called Send(), and a
PL/SQL package SendMailJPkg. These form an interface to JavaMail. At the
end of the script, an anonymous PL/SQL block tests the whole program.

-- SendMail with attachment, the java way;
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SendMail" AS
import java.util.*;
import java.io.*;
import javax.mail.*;
import javax.mail.internet.*;
import javax.activation.*;
public class SendMail {
// Sender, Recipient, CCRecipient, and BccRecipient are comma-
// separated lists of addresses;
// Body can span multiple CR/LF-separated lines;
// Attachments is a ///-separated list of file names;
public static int Send(String SMTPServer,
String Sender,
String Recipient,
String CcRecipient,
String BccRecipient,
String Subject,
String Body,
String ErrorMessage[],
String Attachments) {

// Error status;
int ErrorStatus = 0;

// create some properties and get the default Session;
Properties props = System.getProperties();
props.put("mail.smtp.host", SMTPServer);
Session session = Session.getDefaultInstance(props, null);

try {
// create a message;
MimeMessage msg = new MimeMessage(session);

// extracts the senders and adds them to the message;
// Sender is a comma-separated list of e-mail addresses as
// per RFC822;
{
InternetAddress[] TheAddresses =
InternetAddress.parse(Sender);
msg.addFrom(TheAddresses);
}

// extract the recipients and assign them to the message;
// Recipient is a comma-separated list of e-mail addresses
// as per RFC822;
{
InternetAddress[] TheAddresses =
InternetAddress.parse(Recipient);
msg.addRecipients(Message.RecipientType.TO,
TheAddresses);
}

// extract the Cc-recipients and assign them to the
// message;
// CcRecipient is a comma-separated list of e-mail
// addresses as per RFC822;
if (null != CcRecipient) {
InternetAddress[] TheAddresses =
InternetAddress.parse(CcRecipient);
msg.addRecipients(Message.RecipientType.CC,
TheAddresses);
}

// extract the Bcc-recipients and assign them to the
// message;
// BccRecipient is a comma-separated list of e-mail
// addresses as per RFC822;
if (null != BccRecipient) {
InternetAddress[] TheAddresses =
InternetAddress.parse(BccRecipient);
msg.addRecipients(Message.RecipientType.BCC,
TheAddresses);
}

// subject field;
msg.setSubject(Subject);

// create the Multipart to be added the parts to;
Multipart mp = new MimeMultipart();

// create and fill the first message part;
{
MimeBodyPart mbp = new MimeBodyPart();
mbp.setText(Body);

// attach the part to the multipart;
mp.addBodyPart(mbp);
}

// attach the files to the message;
if (null != Attachments) {
int StartIndex = 0, PosIndex = 0;
while (-1 != (PosIndex = Attachments.indexOf("///",
StartIndex))) {
// create and fill other message parts;
MimeBodyPart mbp = new MimeBodyPart();
FileDataSource fds =
new FileDataSource(Attachments.substring(StartIndex,
PosIndex));
mbp.setDataHandler(new DataHandler(fds));
mbp.setFileName(fds.getName());
mp.addBodyPart(mbp);
PosIndex += 3;
StartIndex = PosIndex;
}
// last, or only, attachment file;
if (StartIndex < Attachments.length()) {
MimeBodyPart mbp = new MimeBodyPart();
FileDataSource fds =
new FileDataSource(Attachments.substring(StartIndex));
mbp.setDataHandler(new DataHandler(fds));
mbp.setFileName(fds.getName());
mp.addBodyPart(mbp);
}
}

// add the Multipart to the message;
msg.setContent(mp);

// set the Date: header;
msg.setSentDate(new Date());

// send the message;
Transport.send(msg);
} catch (MessagingException MsgException) {
ErrorMessage[0] = MsgException.toString();
Exception TheException = null;
if ((TheException = MsgException.getNextException()) !=
null)
ErrorMessage[0] = ErrorMessage[0] + "\n" +
TheException.toString();
ErrorStatus = 1;
}
return ErrorStatus;
}
}
/
show errors java source "SendMail"

CREATE OR REPLACE PACKAGE SendMailJPkg AS
-- EOL is used to separate text line in the message body;
EOL CONSTANT STRING(2) := CHR(13) || CHR(10);

TYPE ATTACHMENTS_LIST IS
TABLE OF VARCHAR2(4000);

-- high-level interface with collections;
FUNCTION SendMail(SMTPServerName IN STRING,
Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING DEFAULT '',
BccRecipient IN STRING DEFAULT '',
Subject IN STRING DEFAULT '',
Body IN STRING DEFAULT '',
ErrorMessage OUT STRING,
Attachments IN ATTACHMENTS_LIST DEFAULT NULL)
RETURN NUMBER;
END SendMailJPkg;
/
show errors

CREATE OR REPLACE PACKAGE BODY SendMailJPkg AS

PROCEDURE ParseAttachment(Attachments IN ATTACHMENTS_LIST,
AttachmentList OUT VARCHAR2) IS
AttachmentSeparator CONSTANT VARCHAR2(12) := '///';
BEGIN
-- boolean short-circuit is used here;
IF Attachments IS NOT NULL AND Attachments.COUNT > 0 THEN
AttachmentList := Attachments(Attachments.FIRST);

-- scan the collection if there is more than one element. If there
-- is not, skip the next part for parsing elements 2 and above. If there
-- is, skip the first element since it has been already processed

IF Attachments.COUNT > 1 THEN

FOR I IN Attachments.NEXT(Attachments.FIRST) .. Attachments.LAST LOOP
AttachmentList := AttachmentList || AttachmentSeparator ||
Attachments(I);
END LOOP;
ELSE
-- whe have to terminate the list with the one element with /// for the java function

AttachmentList := AttachmentList || AttachmentSeparator;
END IF;
ELSE
AttachmentList := '';
END IF;
END ParseAttachment;

-- forward declaration;
FUNCTION JSendMail(SMTPServerName IN STRING,
Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING,
BccRecipient IN STRING,
Subject IN STRING,
Body IN STRING,
ErrorMessage OUT STRING,
Attachments IN STRING) RETURN NUMBER;

-- high-level interface with collections;
FUNCTION SendMail(SMTPServerName IN STRING,
Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING,
BccRecipient IN STRING,
Subject IN STRING,
Body IN STRING,
ErrorMessage OUT STRING,
Attachments IN ATTACHMENTS_LIST) RETURN NUMBER IS
AttachmentList VARCHAR2(4000) := '';
AttachmentTypeList VARCHAR2(2000) := '';
BEGIN
ParseAttachment(Attachments,
AttachmentList);
RETURN JSendMail(SMTPServerName,
Sender,
Recipient,
CcRecipient,
BccRecipient,
Subject,
Body,
ErrorMessage,
AttachmentList);
END SendMail;

-- JSendMail's body is the java function SendMail.Send();
-- thus, no PL/SQL implementation is needed;
FUNCTION JSendMail(SMTPServerName IN STRING,
Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING,
BccRecipient IN STRING,
Subject IN STRING,
Body IN STRING,
ErrorMessage OUT STRING,
Attachments IN STRING) RETURN NUMBER IS
LANGUAGE JAVA
NAME 'SendMail.Send(java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String[],
java.lang.String) return int';
END SendMailJPkg;
/
show errors

var ErrorMessage VARCHAR2(4000);
var ErrorStatus NUMBER;

-- enable SQL*PLUS output;
SET SERVEROUTPUT ON
-- redirect java output into SQL*PLUS buffer;
exec dbms_java.set_output(5000);
BEGIN
:ErrorStatus := SendMailJPkg.SendMail(
SMTPServerName => 'gmsmtp03.oraclecorp.com',
Sender => 'cesare.cervini@oracle.com',
Recipient => 'ccervini@ch.oracle.com',
CcRecipient => '',
BccRecipient => '',
Subject => 'This is the subject line: Test JavaMail',
Body => 'This is the body: Hello, this is a test' ||
SendMailJPkg.EOL || 'that spans 2 lines',
ErrorMessage => :ErrorMessage,
Attachments => SendMailJPkg.ATTACHMENTS_LIST(
'/export/home/osupport/on.lst',
'/export/home/osupport/sqlnet.log.Z'
)
);
END;
/
print


Conclusion
----------

This article shows the real benefit behind the whole Java concept: portability
and easy deployment.


Related Documents
-----------------

Note 73321.1 "Mail Processing from within JSP / PL/SQL" (another approach)

http://java.sun.com/products/javamail

JavaMail and JavaBean Extension Framework documentation from Sun Microsystems.

Java Stored Procedures Developer's Guide (8.1.6)

Java Developer's Guide (8.1.6)