用PL/SQL Java存储过程发送带附件的邮件(使用第三方Java API)
来源:互联网 发布:电商大数据 电子书 编辑:程序博客网 时间:2024/05/29 18:26
Overview
This article shows how to send email using Java Stored Procedures (JSP) and the Sun Microsystems JavaMail package, attachments can also be sent. This article can be used as a guideline by any programmer who needs to implement the functionality of sending email from the Oracle 9i database. 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/9i 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
The first step is to install the java development and runtime environment in the database.
The next step is to download JavaMail and the Javabeans Activation Framework:
- JavaMailTM API Implementation Version 1.2
- JavaBeans Activation Framework 1.0.1 release
You will get two ZIP-Files: javamail-1_2.zip and jaf1_0_1.zip. The archives 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.
On Windows 2000
loadjava.bat -user sys/password -resolve -synonym activation.jar
loadjava.bat -user sys/password -resolve -synonym mail.jarOn Unix
loadjava -user sys/password -resolve -synonym activation.jar
loadjava -user sys/password -resolve -synonym mail.jarFrom SQLPLUS
sqlplus /nolog
connect sys/manager as sysdba;SQL> call sys.dbms_java.loadjava('-v -r -grant PUBLIC -synonym jaf-1.0.1\activation.jar');
SQL> call sys.dbms_java.loadjava('-v -r -grant PUBLIC -synonym javamail-1.2\mail.jar');
Warning
If you are loading classes into Oracle V901, 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 <init> void (java.lang.String,
java.lang.String): cannot access class$java$io$InputStream
verifier is replacing byte code at <init> 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 to 1.2.1. 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.
Configuration
Once the classes have been loaded, you may need to resolve permission issues using the following statements. Please adjust the PATH of your attachments in the third call (E.g. C:\Users\Zahn\Work\*).
sqlplus /nolog
connect sys/manager as sysdba;SQL> exec dbms_java.grant_permission('SCOTT','java.util.PropertyPermission','*','read,write');
SQL> exec dbms_java.grant_permission('SCOTT','java.net.SocketPermission','*','connect, resolve');
SQL> exec dbms_java.grant_permission('SCOTT','java.io.FilePermission','C:\Users\Zahn\Work\*','read, write');
Install Java Code to send mail with attachments
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.
sqlplus scott/tiger
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.akadia.com", 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;
} // End Send Class
} // End of public class SendMail
/
Java created.
SQL> show errors java source "SendMail"
No errors.
Install PL/SQL Package which forms an interface to JavaMail
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;
/
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, skip first element since it has been
-- already processed;
-- accommodate for sparse collections;
FOR I IN Attachments.NEXT(Attachments.FIRST) .. Attachments.LAST LOOP
AttachmentList := AttachmentList || AttachmentSeparator || Attachments(I);
END LOOP;
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;
/
Package created.
Package body created.
Test the whole program from within SQLPLUS
sqlplus scott/tiger
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 => 'localhost',
Sender => 'martin dot zahn at akadia dot ch',
Recipient => 'martin dot zahn at akadia dot ch',
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(
'C:\Users\Zahn\Work\sendmail.sql',
'C:\Users\Zahn\Work\ferien-2002.txt'
)
);
END;
/PL/SQL procedure successfully completed.
ERRORMESSAGE
------------
ERRORSTATUS
-----------
0After a few seconds I get the Email on my IMAP enabled Outlook Mailtool !
Conclusion
This article shows the real benefit behind the whole Java concept: portability and easy deployment.
Related Documents
http://metalink.oracle.com
http://java.sun.com/products/javamailJavaMail and JavaBean Extension Framework documentation from Sun Microsystems.
Java Stored Procedures Developer's Guide
Java Developer's GuideDownload Everything from here
原文链接
http://www.akadia.com/services/java_mail_plsql.html
- 用PL/SQL Java存储过程发送带附件的邮件(使用第三方Java API)
- 使用Java发送带附件的邮件
- 用java发送带附件的邮件
- 使用java mail发送带附件的邮件
- java Jmail发送带附件的邮件
- 发送带附件的邮件 java
- 带附件的java.mail邮件发送
- 【java】javamail发送带附件的邮件
- java邮件(带附件发送)
- Java实现邮件发送(带附件)
- java发送带附件邮件
- Java邮件发送带附件
- java发送邮件带附件
- Java发送邮件(带附件)
- Java发送邮件(带附件)
- Java 发送带附件邮件
- Java发送邮件(带附件)
- Java发送邮件(带附件)
- android phone模块流程
- 用C++写的图的算法。
- 设计模式六大原则(1):单一职责原则
- 给Matlab添加库函数
- 设计模式六大原则(2):里氏替换原则
- 用PL/SQL Java存储过程发送带附件的邮件(使用第三方Java API)
- Linux下JDK安装步骤
- MyEclipse 上进行数据库处理(SQL Editor)
- 设计模式六大原则(3):依赖倒置原则
- 数据结构笔记三(20120825)
- 设计模式六大原则(4):接口隔离原则
- 设计模式六大原则(5):迪米特法则
- 算法导论 第14章 14.3 区间树
- 设计模式六大原则(6):开闭原则