Oracle 19C中Java向PL/SQL函数返回Blob的实现方案求助

需求说明 #

在Oracle 19C环境中，使用javax.mail创建邮件，现有代码原本返回错误状态码，现在需要将生成的邮件消息通过msg.writeTo(bos)转换为Blob返回给调用的PL/SQL函数，而非返回错误码，当前尝试的代码未成功，需修改实现该需求。

现有代码 #

原Java源程序 #

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "BarrySendMail" AS
 import java.util.*;
 import java.io.*;
 import java.sql.*;
 import javax.mail.*;
 import javax.mail.internet.*;
 import javax.activation.*;
  
  
 public class BarrySendMail {
  // 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;
   
  //Connection
  Connection con = null;

  // Create some properties and get the default Session;
  Properties props = System.getProperties();
  props.put("blahblah", SMTPServer);
  Session session = Session.getDefaultInstance(props, null);
  session.setDebug(true);
  try {
   // Create a message.
   MimeMessage msg = new MimeMessage(session);
   // ... 此处省略原代码中构建邮件内容、添加附件等逻辑
   
   //Save the message to a binary object **This is my attempt**
   ByteArrayOutputStream bos = new ByteArrayOutputStream();
   ByteArrayInputStream bis = null;
   try{
    msg.writeTo(bos);
    }catch(Exception exp){
    exp.printStackTrace();}
// 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 new ByteArrayInputStream(bos.toByteArray());
   return ErrorStatus; //***Instead, I would like to return bos;
   
  //return ErrorStatus;
  } // End Send Class
 } // End of public class BarrySendMail

原PL/SQL调用函数 #

FUNCTION BarrySendMail(
           SMTPServer 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 blob IS
  LANGUAGE JAVA
  NAME 'BarrySendMail.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 java.sql.Blob';

修改步骤及代码 #

1. 修改Java方法的返回类型及逻辑 #

将方法返回类型从int改为java.sql.Blob，添加Blob对象创建逻辑，同时完善异常处理（错误信息通过ErrorMessage输出，异常时返回null）。

修改后的Java代码：

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "BarrySendMail" AS
 import java.util.*;
 import java.io.*;
 import java.sql.*;
 import javax.mail.*;
 import javax.mail.internet.*;
 import javax.activation.*;
  
  
 public class BarrySendMail {
  // 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 Blob Send(String SMTPServer,
       String Sender,
       String Recipient,
       String CcRecipient,
       String BccRecipient,
       String Subject,
       String Body,
       String ErrorMessage[],
       String Attachments) {
  // 初始化返回的Blob对象
  Blob mailBlob = null;
   
  // 获取Oracle默认数据库连接
  Connection con = null;

  // 配置SMTP属性
  Properties props = System.getProperties();
  props.put("mail.smtp.host", SMTPServer); // 修正原代码的无效属性名
  Session session = Session.getDefaultInstance(props, null);
  session.setDebug(true);
  try {
   // 创建邮件消息并填充内容
   MimeMessage msg = new MimeMessage(session);
   msg.setFrom(new InternetAddress(Sender));
   msg.setRecipients(Message.RecipientType.TO, InternetAddress.parse(Recipient));
   // 处理抄送和密送
   if (CcRecipient != null && !CcRecipient.isEmpty()) {
       msg.setRecipients(Message.RecipientType.CC, InternetAddress.parse(CcRecipient));
   }
   if (BccRecipient != null && !BccRecipient.isEmpty()) {
       msg.setRecipients(Message.RecipientType.BCC, InternetAddress.parse(BccRecipient));
   }
   msg.setSubject(Subject);
   msg.setText(Body);
   
   // 添加附件（如果有）
   if (Attachments != null && !Attachments.isEmpty()) {
       String[] files = Attachments.split("///");
       Multipart multipart = new MimeMultipart();
       for (String filePath : files) {
           FileDataSource fds = new FileDataSource(filePath);
           MimeBodyPart part = new MimeBodyPart();
           part.setDataHandler(new DataHandler(fds));
           part.setFileName(fds.getName());
           multipart.addBodyPart(part);
       }
       msg.setContent(multipart);
   }
   
   // 将邮件写入字节流
   ByteArrayOutputStream bos = new ByteArrayOutputStream();
   msg.writeTo(bos);
   byte[] mailBytes = bos.toByteArray();
   
   // 创建Oracle Blob对象
   con = DriverManager.getConnection("jdbc:default:connection");
   mailBlob = con.createBlob();
   mailBlob.setBytes(1, mailBytes);
   
   // 可选：如果仍需要发送邮件，保留此语句
   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();
  } catch (SQLException sqlExp) {
   ErrorMessage[0] = "数据库操作错误：" + sqlExp.toString();
  } catch (Exception exp) {
   ErrorMessage[0] = "未知错误：" + exp.toString();
  } finally {
   // 关闭数据库连接
   try {
       if (con != null) con.close();
   } catch (SQLException e) {
       e.printStackTrace();
   }
  }
   
   return mailBlob;
  } // End Send method
 } // End of public class BarrySendMail

2. 修正PL/SQL函数签名 #

确保PL/SQL函数的参数类型、返回类型与Java方法完全匹配：

CREATE OR REPLACE FUNCTION BarrySendMail(
           SMTPServer IN VARCHAR2,
           Sender IN VARCHAR2,
           Recipient IN VARCHAR2,
           CcRecipient IN VARCHAR2,
           BccRecipient IN VARCHAR2,
           Subject IN VARCHAR2,
           Body IN VARCHAR2,
           ErrorMessage OUT VARCHAR2,
           Attachments IN VARCHAR2) RETURN BLOB IS
  LANGUAGE JAVA
  NAME 'BarrySendMail.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 java.sql.Blob';
/

关键说明 #

• 使用jdbc:default:connection获取Oracle默认连接，无需额外配置用户名密码。
• 创建Blob对象时使用Oracle JDBC的createBlob()方法，保证Oracle 19C兼容性。
• 异常场景下，错误信息通过ErrorMessage参数输出，方法返回null标识失败；成功时返回邮件对应的Blob对象。
• 原代码中props.put("blahblah", SMTPServer)需修正为mail.smtp.host，否则SMTP配置无效会导致邮件发送失败。

内容的提问来源于stack exchange，提问作者Barry Brierley