:: Artigos - SQL Magazine ::

Envio de E-mails contendo anexos utilizando PL/SQL e JSP (Java Stored Procedure)

Por Leandro Castello Eschiavi
leandroeschiavi@terra.com.br

1 – Carregando e lendo a API JavaMail

Primeiramente, deve-se realizar o download a partir do site http://java.sun.com/products/javamail/index.html. Lá, encontram-se a API JAVAMAIL versão 1.3.2 e o JavaBeansTM Activation Framework extension ou JAF que é utilizado em conjunto com a API JAVAMAIL. Extraia o conteúdo dos arquivos *.ZIP para dentro da pasta C:\Temp, por exemplo, na qual você irá utilizar somente dois arquivos, o mail.jar e o activation.jar. No prompt do DOS digite :

loadjava -u sys/manager@o9i -o -r -v -f -noverify -synonym -g public mail.jar loadjava -u sys/manager@o9i -o -r -v -f -noverify -synonym -g public activation.jar

Onde :

-u sys/manager : Id e senha para sua conta SYS
-o : Para utilização do driver oci8
-r : Verificação e validação das classes JAVA
-v : Lista passo a passo do processo de carregamento das classes JAVA
-f : Recria o processo caso ocorra algum erro
-noverify : Verifica os bytecodes do processo -synonym : Cria sinônimos públicos -g public : Previlégios GRANT para as classes JAVA

Dentro do SQL PLUS digite :

Usuário SCOTT/TIGER

call sys.dbms_java.loadjava('-v -r -grant PUBLIC -synonym activation.jar') call sys.dbms_java.loadjava('-v -r -grant PUBLIC -synonym mail.jar')

Usuário SYS/MANAGER

exec dbms_java.grant_permission('SCOTT','java.util.PropertyPermission','*','read,write'); exec dbms_java.grant_permission('SCOTT','java.net.SocketPermission','*','connect, resolve');
exec bms_java.grant_permission('SCOTT','java.io.FilePermission','C:\*','read,write');

2 – Criando a JSP (Java Stored 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 SMTPServer,                            
                            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();
        //Inserção do endereço SMTP

        props.put("smtp.sao.terra.com.br", SMTPServer);
        Session session = Session.getDefaultInstance(props, null);
 
        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 SendMailJPkg 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 SendMail(SMTPServerName IN STRING,
                     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 SendMailJPkg;
/

3.2 – Criando o corpo da Package PL/SQL

CREATE OR REPLACE PACKAGE BODY SendMailJPkg AS
   PROCEDURE ParseAttachment(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 ParseAttachment;
   -- Forward declaration
   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 NUMBER;
   -- High-level interface with collections
   FUNCTION SendMail(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 ATTACHMENTS_LIST) RETURN NUMBERIS
      AttachmentList VARCHAR2(4000) := '';
      AttachmentTypeList VARCHAR2(2000) := '';
   BEGIN
      ParseAttachment(Attachments,AttachmentList);
      RETURN JSendMail(SMTPServerName,
                       Sender,
                       Recipient,
                       CcRecipient,
                       BccRecipient,
                       Subject,
                       Body,
                       ErrorMessage,
                       AttachmentList);
   END SendMail;
   -- JSendMail's body is the java function SendMail.Send()
   -- thus, no PL/SQL implementation is needed
   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 NUMBER IS
   LANGUAGE JAVA
   NAME 'SendMail.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 int';
END SendMailJPkg;
/

4 – Checando o funcionamento do E-mail (SQL PLUS)

var ErrorMessage VARCHAR2(4000);
var ErrorStatus NUMBER;
-- Aciona a saída no SQL*PLUS
SET SERVEROUTPUT ON
-- Redireciona a saída java dentro do buffer SQL*PLUS
exec dbms_java.set_output(5000);
BEGIN
:ErrorStatus := SendMailJPkg.SendMail(
                SMTPServerName => 'localhost',
                Sender    => 'leandro@terra.com.br',
                Recipient => 'leandroeschiavi@terra.com.br',
                CcRecipient => '',
                BccRecipient => '',
                Subject   => 'This is the subject line:
                Test JavaMail',
                Body => 'This is the body: Hello, this is a test' ||
                         SendMailJPkg.EOL || 'that spans 2 lines',
                ErrorMessage => :ErrorMessage,
                Attachments => SendMailJPkg.ATTACHMENTS_LIST(
'C:\ppc0010r.pdf'                                                               ,'C:\teste.txt'
                                                           )
                                        );
END;
/print