最近工作中需要使用oracle发送带附件的邮件，在网上查了些资料，发现最简单的办法是使用oracle提供的utl_mail包发送邮件，但是由于该包没有提供smtp邮件服务器用户名密码的参数设置，所以如果smtp服务器需要验证，用这个方法似乎就行不通了，而且该方法仅适用于oracle10g及其以上版本。该方法参考至：http://www.cnblogs.com/xublogs/archive/2011/06/20/2292201.html
接下来就只能另辟蹊径了，通过寻找，发现utl_smtp包发送带附件的邮件也是可行的，具体方法可参考http://wenku.baidu.com/view/c034088bcc22bcd126ff0ce7.html###，但是这个方法似乎挺麻烦的，看着这么长的代码就头晕，故我没有进行测试。。。
最后国外的一个网站上找到一篇文章，是使用java procedure发送邮件，而且可以带附件，看着也比较简单，于是尝试了一下，果然很好使。先贴上链接：http://www.akadia.com/services/java_mail_plsql.html
- 向oracle加载jar包：
On Windows 2000
loadjava.bat -user sys/password -resolve -synonym activation.jarloadjava.bat -user sys/password -resolve -synonym mail.jar
On Unix
loadjava -user sys/password -resolve -synonym activation.jarloadjava -user sys/password -resolve -synonym mail.jar
From SQLPLUS
sqlplus /nologconnect 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');
注意要修改sys用户的密码，并且activation.jar和mail.jar需要使用绝对路径，$ORACLE_HOME/lib 目录下就有这两个jar包。另外，本人只尝试过在linux上加载jar包，其他两种方式没有尝试。
如果loadjava时没有任何提示，则表示成功。如果提示:
Error while creating resource META-INF/MANIFEST.MF ORA-06550: line 1, column 87:PLS-00201: identifier 'NameFromLastDDL' must be declaredORA-06550: line 1, column 79:PL/SQL: Statement ignored
的错误，则表示oracle的jvm可能安装失败了，具体可以通过
先检查是否存在这个对象：
SELECT OWNER,
OBJECT_NAME,
OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE '%NameFromLastDDL%' ;
如果没有，使用：
select count(*) from dba_objects where object_type like 'JAVA%';
select comp_name, status from DBA_REGISTRY where upper(comp_name) like '%JAVA%' ;这两个语句进行验证，正常情况下的结果应该是：
SQL> select count(*) from dba_objects where object_type like 'JAVA%';
COUNT(*)
10605
JServer JAVA Virtual Machine VALID
Oracle9i Java Packages VALID
Oracle XDK for Java VALID
COUNT(*)
10605
SQL> select comp_name, status from DBA_REGISTRY where upper(comp_name) like '%JAVA%' ;COMP_NAME STATUS
JServer JAVA Virtual Machine VALID
Oracle9i Java Packages VALID
Oracle XDK for Java VALID
要想解决这个问题到$ORACLE_HOME/javavm/install/ 目录下执行initjvm.sql文件即可（这个我没有尝试过，是下面论坛上提供的解决方法）
摘自：https://forums.oracle.com/forums/thread.jspa?threadID=350921&start=0&tstart=0
- 接下来就是进行授权，使用sysdba身份登录oracle，并执行一下命令（注意修改SCOTT用户名为需要授权使用的用户名，字母大写。并将最后一条语句的路径修改成附件存放的路径，一遍用户有权限读取该路径下的文件并能以附件形式发送）：
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');
- 接下来就是编写java存储过程了，代码如下（拿过去基本就可以使用，我就改了下名字和命名习惯并增加了几个参数）：
java procedure：
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 pSMTPServer,
String port,
boolean isAuth,
final String uname,
final String upwd,
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", pSMTPServer);
props.put("mail.smtp.port", port);
props.put("mail.smtp.auth", isAuth?"true":"false");
Session session = Session.getDefaultInstance(props, new Authenticator(){
protected PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(uname,upwd);
}
});
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
存储过程：
CREATE OR REPLACE PACKAGE PKG_SEND_MAIL 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 SEND_MAIL(SMTPSERVERNAME IN STRING,
PORT IN STRING DEFAULT '25',
ISAUTH IN BOOLEAN DEFAULT FALSE,
UNAME IN STRING DEFAULT '',
UPWD IN STRING DEFAULT '',
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 PKG_SEND_MAIL;
/
CREATE OR REPLACE PACKAGE BODY PKG_SEND_MAIL AS
PROCEDURE PARSE_ATTACHMENT(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 PARSE_ATTACHMENT;
-- Forward declaration
FUNCTION J_SEND_MAIL(SMTPSERVERNAME IN STRING,
PORT IN STRING,
ISAUTH IN BOOLEAN,
UNAME IN STRING,
UPWD 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 SEND_MAIL(SMTPSERVERNAME IN STRING,
PORT IN STRING DEFAULT '25',
ISAUTH IN BOOLEAN DEFAULT FALSE,
UNAME IN STRING DEFAULT '',
UPWD IN STRING DEFAULT '',
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) := '';
BEGIN
PARSE_ATTACHMENT(ATTACHMENTS,
ATTACHMENTLIST);
RETURN J_SEND_MAIL(SMTPSERVERNAME,
PORT,
ISAUTH,
UNAME,
UPWD,
SENDER,
RECIPIENT,
CCRECIPIENT,
BCCRECIPIENT,
SUBJECT,
BODY,
ERRORMESSAGE,
ATTACHMENTLIST);
END SEND_MAIL;
-- JSendMail's body is the java function SendMail.Send()
-- thus, no PL/SQL implementation is needed
FUNCTION J_SEND_MAIL(SMTPSERVERNAME IN STRING,
PORT IN STRING,
ISAUTH IN BOOLEAN,
UNAME IN STRING,
UPWD 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,
boolean,
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[],
java.lang.String) return int';
END PKG_SEND_MAIL;
/- 在数据库中编译成功之后，即可尝试使用该程序发送邮件（测试代码如下，注意修改用户名密码）：
DECLARE ERRORMESSAGE VARCHAR2(4000); ERRORSTATUS NUMBER; BEGIN ERRORSTATUS := PKG_SEND_MAIL.SEND_MAIL(SMTPSERVERNAME => 'smtp.163.com', PORT => '25', ISAUTH => TRUE, UNAME => 'sender@163.com', UPWD => '********', SENDER => 'sender.163.com', RECIPIENT => 'recipient@gmail.com', CCRECIPIENT => '', BCCRECIPIENT => '', SUBJECT => 'This is the subject line: Test JavaMail', BODY => 'This is the body: Hello, this is a test' || 111111 || 'that spans 2 lines', ERRORMESSAGE => ERRORMESSAGE, ATTACHMENTS => PKG_SEND_MAIL.ATTACHMENTS_LIST('/tmp/glibc.txt','/tmp/glibc.txt')); DBMS_OUTPUT.PUT_LINE(ERRORSTATUS || ' ' || ERRORMESSAGE); END;
由于博客中无法上传文档，所以将源码上传csdn资源，大家需要的话可以去搜索下载（http://download.csdn.net/detail/zhy0511/4782795）。
5865
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
