从Java返回blob以调用PL/SQL函数

Question

Oracle 19C。

对于某些人来说，这应该是一个简单的语法问题。我不熟悉Java领域，因为我正在使用javax.mail创建电子邮件。

我使用的Java代码直接来自Oracle的帮助，所以我认为一切都可以。但我想做的是，返回整个消息，这样我就可以将其保存为blob，而不是从Java代码中返回错误号。下面是代码的开头。省略了创建电子邮件的所有部分，然后继续使用我插入的(未成功(保存邮件的代码。我想我需要几行代码来替换"；return ErrorStatus">

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 attempy**
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

这反过来又被一个函数调用：

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';

本质上，我在问如何返回"；msg.writeTo(bos("作为调用函数的blob。

提前感谢

Barry

Answer 1

所以我做了一个变通办法，把它保存到一个表中。

对于Java程序员来说，这可能看起来很可怕，但似乎可以工作。

//Save the message to 
ByteArrayOutputStream bos = new ByteArrayOutputStream();
ByteArrayInputStream bis = null;
//memory = new MemoryStream ();
try  {
msg.writeTo(bos);
int i = bos.toByteArray().length;
byte[] array = new byte[i];
array = bos.toByteArray();

//insertIntoProducts(desBlob);
Connection conn = DriverManager.getConnection("jdbc:default:connection:");
Blob desBlob = conn.createBlob();
desBlob.setBytes(1, array);
//PreparedStatement ps2 = conn.prepareStatement("insert into mytable values (hextoraw(?))");
PreparedStatement ps2 = conn.prepareStatement("insert into barry_testtable(blobcolumn) values (?)");
ps2.setBytes(1,array);
try{
int rowsAffected = ps2.executeUpdate();
System.out.println(rowsAffected); //1
} catch (SQLException e) {
System.err.format("SQL State: %sn%s", e.getSQLState(), e.getMessage());
} catch (Exception e) {
e.printStackTrace();
}

}catch(Exception exp){
exp.printStackTrace();}