Oracle + Java - Mail Issue using JavaMail

雖然Oracle的Alert有很強大的Mail功能
但是當需求上要求有多個附檔或多個收件人時
應用上並不是那麼容易

既然Oracle 9i上已經與Java有滿不錯地結合
因此可以善用Java網路上廣大的Free Resources
在mail方面..
Java Mail就是一個容易上手的工具...


1. 確認必要Class

在9i資料庫中查Java Class
可以發現Javax.mail與Javax.activation的package已經在資料庫中了
因此要建置Java Mail並不需要再用loadjava置入額外的class
建置Java Mail主要參考Metalink的Note:120994.1

2. 建立Java Source並Compile

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "SendMail"
AS import java.util.*;
import java.io.*;
import javax.mail.*;
import javax.mail.internet.*;
import javax.activation.*;
public class SendMail {
// Zhxiang -test 20060918
// 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;
System.setProperty("mail.mime.charset", "utf-8" ); //zhxiang 20060918
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);
//msg.setHeader("Subject", MimeUtility.encodeText(Subject, "big5", null)); //zhxiang --20060918 --test
//msg.setSubject(MimeUtility.encodeText(Subject, "big5", null)); //zhxiang --20060918 --test
// 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);
mbp.setContent(Body,"text/html ;charset=utf-8");//--zhxiang 20060918

// 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;
}
}
/

其中中文CJK的問題
以utf-8編碼解決
在這一行　mbp.setContent(Body,"text/html ;charset=utf-8");

3. PL/SQL call Java

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;


FUNCTION SendMail(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('123.456.789.111', --mail server
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;
/

在這邊我同名了 JSendMail的function
使開發者在不知道mail server ip之下一樣可以send mail
而且當mail server ip改變時
只要回來改這個程式...
不必動到所有的application...


4. Grant Java Permission

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

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

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

5. Application

如此可以使用 sendmailjpkg.JSendMail(...)來發mail了