Skip to Main Content

Java and JavaScript in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Returning a blob from Java to calling PL/SQL function

BarryBAug 11 2022

Hi Experts,
Oracle 19C.
This should hopefully be a simple syntax question for somebody. I am in unfamiliar Java territory as I am using javax.mail to create an email.
The Java code that I am using is straight out of the Oracle help so I assume it's all OK. But what I would like to do is, instead of returning the error number from the Java code, to return the whole message so that I can save it as a blob. Below is the beginning of the code. leaving out all of the bits that create the email message, then continuing with the code that I have (unsuccessfully) inserted to save the message. I think I need a couple of lines of code to replace "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);

etc...then continues...

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

This is in turn called by a function:
 

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

Essentially I'm asking how to return "msg.writeTo(bos);" as a blob to my calling function.

Thanks in advance,
Barry

Comments
Post Details
Added on Aug 11 2022
1 comment
661 views