Re: Sending mail from a SP

From: <haaseg_at_my-deja.com>
Date: Mon, 18 Oct 1999 14:55:10 GMT
Message-ID: <7ufcc9$k5n$1@nnrp1.deja.com>

If you are using Oracle 8i you can develop a java stored procedure for sending mails.
I've done this 3 month ago but didn't put the solution in production because we integrated the mail functionality in the java client.

Steps for developing such a procedures are:

#################################

1. load java mail archives as sys
#################################

loadjava -u sys/<password>@<db> -v -r -f activation.jar loadjava -u sys/<password>@<db> -v -r -f mail.jar (uncompressed)

You can get activation.jar/mail.jar from http://java.sun.com/products/javamail

Oracle 8i expects the jar file to be uncompressed but mail.jar can be compressed (I've needed 2 hours to learn this). To uncompress it use:
jar x mail.jar
jar cvf0 mail.jar com javax meta-inf

#################################

2. develop java mail procedure
#################################

You can use this as a starting point:

package TestMail;

import java.util.*;
import java.io.*;

import javax.mail.*;
import javax.mail.internet.*;
import javax.activation.*;

public class SendMail
{
  static void print(String out)
  {
    System.out.println(out);
  }

  public static void sendFrom(
    String sendTo,
    String subject,
    String bodyText,
    String from)
  {
    // mail variables
    Properties props;
    String mailHost = "<smtp host>";     Session session;

    // create some properties and get the default Session     props = System.getProperties();
    props.put("mail.smtp.host", mailHost);

    session = Session.getDefaultInstance(props, null);     session.setDebug(false);

    sendMail(session,sendTo,from,subject,bodyText);   }

  static void sendMail(
    Session session,
    String sendTo,
    String from,
    String subject,
    String bodyText)
  {
    try {

      // create a mime message
      MimeMessage mail = new MimeMessage(session);
      mail.setFrom(new InternetAddress(from));
      InternetAddress[] address = {new InternetAddress(sendTo)};
      mail.setRecipients(Message.RecipientType.TO, address);
      mail.setSubject(subject);

      // create and fill the first message part (bodyText)
      MimeBodyPart bodyPart1 = new MimeBodyPart();
      bodyPart1.setText(bodyText);

      // create the Multipart and its parts to it
      Multipart multiPart = new MimeMultipart();
      multiPart.addBodyPart(bodyPart1);

      // add the Multipart to the message
      mail.setContent(multiPart);

      // set the Date: header
      mail.setSentDate(new Date());

      // send the message
      Transport.send(mail);

    }
    catch (MessagingException mex)
    {

      print("SendTo:     " + sendTo);
      print("From:       " + from);
      print("Subject:    " + subject);
      print("BodyText:   " + bodyText);

      mex.printStackTrace();
      Exception ex = null;
      if ((ex = mex.getNextException()) != null) {
        ex.printStackTrace();
      }

    }
  }
}

#################################

3. load java mail procedure
#################################

loadjava -u <user>/<password>@<db> -v -r -f TestMail.jar -- execute dbms_java.set_output(2000)

#################################

4. define call specification
#################################

CREATE OR REPLACE PROCEDURE send_mail_from (

  sendTo   VARCHAR2,
  subject  VARCHAR2,
  bodyText VARCHAR2,

  sendFrom VARCHAR2)
AS LANGUAGE JAVA
NAME
'TestMail.SendMail.sendFrom(java.lang.String,java.lang.String,java.lang. String,java.lang.String)';

#################################

The disadvantages of this method are:
1. You have to know a lot of JavaMail API, Java Stored Procedures and perhaps JDBC
2. You need Oracle 8i

The advantages are
1. The solution is clean and doesn't depend on a (perhaps unstable) dbms pipe.
2. You can easily get data from the database (e.g. attachments) because you are "in the database".
3. JavaMail API is very powerfull so you can do what you want.

In article <yiPL3.70767$L6.7841083_at_news1.rdc1.on.wave.home.com>,   "marasoft" <marasoft_at_home.com> wrote: > Does anyone know if it is possible to send an email message from a stored

> procedure (PL/SQL)? Is there any package that can do this?
>
> Thank's
> David
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Oct 18 1999 - 09:55:10 CDT