ZRMS
Setup for Sending Email with Attachments from PL/SQL using Java Stored Procedures

1. Download JavaMail and the Javabeans Activation Framework:
 	JavaMailTM API Implementation Version 1.2 
 	JavaBeans Activation Framework 1.0.1 release 
      

2. 

From SQLPLUS
Go to command Prompt.
Go to Dir. where mail.jar and activation.jar are extracted.
Then logon to sql as below :
sqlplus /nolog
connect sys/manager as sysdba;
SQL> call sys.dbms_java.loadjava('-v -r -grant PUBLIC -synonym activation.jar');
SQL> call sys.dbms_java.loadjava('-v -r -grant PUBLIC –synonym mail.jar');

 

3. Adjust the PATH of attachments in the third call 

(E.g. E:\PO_Attach\1\2\*)

4. Issue the resolve permission as below
sqlplus /nolog
connect sys/manager as sysdba;
SQL> exec dbms_java.grant_permission('CVN_HO','java.util.PropertyPermission','*','read,write');

SQL> exec dbms_java.grant_permission('CVN_HO','java.net.SocketPermission','*','connect, resolve');

SQL> exec dbms_java.grant_permission('CVN_HO','java.io.FilePermission','E:\email\1\2\*','read, write');

5. Connect to Schema e.g. CVN_HO and issue the resolve permission as below
SQL> exec dbms_java.grant_permission('CVN_HO','java.util.PropertyPermission','*','read,write');

SQL> exec dbms_java.grant_permission('CVN_HO','java.net.SocketPermission','*','connect, resolve');

SQL> exec dbms_java.grant_permission('CVN_HO','java.io.FilePermission','E:\email\1\2\*','read, write');


 

6. Next, the following SQL*PLUS script should be executed. It creates a Java class named “SendMail” with only one member function called Send(), and PL/SQL package “SendMailPKG”. These form an interface to JavaMail. 

Conn to Sys as sysdba;

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();
        props.put("mail.akadia.com", 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
/
Java created.

SQL> show errors java source "SendMail"
No errors.




 

7. Create PL/SQL package as below which forms an Interface to JavaMail

Conn sys as sysdba;


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



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 NUMBER IS
      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;
/




8.	Give grants to CVN_HO schema to access “SendMailJPkg” package.

GRANT ALL ON SENDMAILJPKG TO CVN_HO;



9.  Create a Procedure “Pkg_Send_PO_Email” which send the mail to  Vendor.

 Conn CVN_HO/.....@MAX;


CREATE OR REPLACE PACKAGE BODY Pkg_Send_PO_Email AS
  PROCEDURE P_SEND_PO_EMAILS
  IS
    CURSOR cur_po_reports IS
      SELECT po_no, vendor_email, created_by
      FROM   po_email
      WHERE  email_sent_ind = 'N'
      AND    vendor_email IS NOT NULL;
    rec_po_reports         cur_po_reports%ROWTYPE;
    error_status           NUMBER;
    ErrorMessage           VARCHAR2(30000);
    /* Email related Parameter variables. */
    v_ip_address           parameter_master.value%TYPE;
    v_email_sender         user_master.email%TYPE;
    v_email_subject        parameter_master.value%TYPE;
    v_email_text           parameter_master.value%TYPE;
    v_po_document_path     parameter_master.value%TYPE;
    v_sender_email_id      parameter_master.value%TYPE;
    v_attachment           VARCHAR2(100);
    v_missing_parameters   EXCEPTION;
  BEGIN
    DBMS_JAVA.SET_OUTPUT(5000);
    DBMS_OUTPUT.PUT_LINE('Sending mails ... started !');
    /* Fetch the Email related Parameters from the Parameter Master table. */
    BEGIN
      SELECT pm1.value, pm2.value, pm3.value, pm4.value, pm5.value
      INTO   v_ip_address, v_email_subject, v_email_text,
             v_po_document_path, v_sender_email_id
      FROM   parameter_master pm1, parameter_master pm2, parameter_master pm3,
             parameter_master pm4, parameter_master pm5
      WHERE  pm1.type_code = 16
      AND    pm1.type_code = pm2.type_code
      AND    pm1.type_code = pm3.type_code
      AND    pm1.type_code = pm4.type_code
      AND    pm1.type_code = pm5.type_code
      AND    pm1.value_code = 1
      AND    pm2.value_code = 2
      AND    pm3.value_code = 3
      AND    pm4.value_code = 4
      AND    pm5.value_code = 5;
	 
	  IF v_ip_address IS NULL
      THEN
        RAISE_APPLICATION_ERROR(-20001, 'Email setting ''Email Server IP Address'' not found in Parameter Master.', FALSE);
      END IF;
      IF v_email_subject IS NULL
      THEN
        RAISE_APPLICATION_ERROR(-20002, 'Email setting ''Email Subject'' not found in Parameter Master.', FALSE);
      END IF;
      IF v_email_text IS NULL
      THEN
        RAISE_APPLICATION_ERROR(-20003, 'Email setting ''Email Text'' not found in Parameter Master.', FALSE);
      END IF;
      IF v_po_document_path IS NULL
      THEN
        RAISE_APPLICATION_ERROR(-20004, 'Email setting ''PO Document Path'' not found in Parameter Master.', FALSE);
      END IF;
      IF v_sender_email_id IS NULL
      THEN
        RAISE_APPLICATION_ERROR(-200005, 'Email setting ''Sender/Default Email ID'' not found in Parameter Master.', FALSE);
      END IF;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20000, 'Email Setting parameter(s) not found in Parameter Master.', FALSE);
      WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20998, SQLERRM, FALSE);
    END;
    FOR rec_po_reports IN cur_po_reports
    LOOP
      DBMS_OUTPUT.PUT_LINE(rec_po_reports.po_no || 'Sending mail to ' || rec_po_reports.vendor_email);
      /* Fetch the Sender Email address from the User Master table. */
      BEGIN
        SELECT NVL(email, v_sender_email_id)
        INTO   v_email_sender
        FROM   user_master
        WHERE  user_id = rec_po_reports.created_by;
        IF v_email_sender IS NULL
        THEN
	  RAISE_APPLICATION_ERROR(-20011, 'Sender Email address neither found in User Master nor in Parameter Master.', FALSE);
        END IF;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
	  RAISE_APPLICATION_ERROR(-20011, 'Sender Email address neither found in User Master nor in Parameter Master.', FALSE);
        WHEN OTHERS THEN
          RAISE_APPLICATION_ERROR(-20999, SQLERRM, FALSE);
      END;
      /* Create the attachment with first attachment as the PO Report (the PDF file)
         and the second attachment as NULL. Here the second attachment is taken as
         NULL to avoid an error generation 'ORA-06502: PL/SQL: numeric or value error',
         while calling the function 'Attachments_List' in the package 'SENDMAILJPKG1'.
      */
      v_attachment := v_po_document_path || 'PO_' || rec_po_reports.po_no || '.pdf';
	DBMS_OUTPUT.PUT_LINE('v_attachment - ' || v_attachment);
	  error_status := SYS.SENDMAILJPKG.SENDMAIL
                      (
                        SMTPServerName => v_ip_address,
                        Sender         => v_email_sender,
                        Recipient      => rec_po_reports.vendor_email,
                        CCRecipient    => 'pradeep@maxhypermarkets.com',
                        BCCRecipient   => 'pradeep@maxhypermarkets.com',
                        Subject        => v_email_subject || ' : ' || rec_po_reports.po_no,
                        Body           => v_email_text,
                        ErrorMessage   => ErrorMessage,
                        Attachments    => SYS.SENDMAILJPKG.Attachments_List(v_attachment, NULL)
                      );
      DBMS_OUTPUT.PUT_LINE(error_status);
      /* Update the 'sent_email_ind' to 'Y' and the 'sent_date' to 'SYSDATE'
         after sending a mail to the vendor.
      */
      UPDATE po_email
      SET    email_sent_ind = 'Y',
             sent_date = SYSDATE
      WHERE  po_no = rec_po_reports.po_no;
    END LOOP;
    COMMIT;
  END P_SEND_PO_EMAILS;
END Pkg_Send_PO_Email;
/

 
9.	Set the Parameter_Master table as Below :


 




10.	Execute the “P_SEND_PO_EMAILS” procedure as below :

Conn cvn_ho/…..@max


SQL> EXEC Pkg_Send_PO_Email. P_SEND_PO_EMAILS

PL/SQL procedure successfully completed.


After a few seconds check the email in SMTP enabled Outlook Mail !!!!

--------------------end-----------------
Following is the anonymous block with which i am trying to test whether am gertting the mail in my outlook. 
i am getting the error_status as zero i.e. it is successful.But am not getting any mail.

Declare
 error_status  varchar2(5);
 errormessage varchar2(100);
Begin
 error_status := sys.sendmailjpkg.sendmail
                      (
                        smtpservername => '172.16.150.50',
                        sender         => 'jayaram.p@maxhypermarkets.com',
                        recipient      =>          'shashanka.10@gmail.com' ,--rec_po_reportsvendor_email,
                        ccrecipient    => 'vikas.k@maxhypermarkets.com',
                        bccrecipient   =>  'shashanka.k@maxhypermarkets.com' , --:v_email_sender,
                        subject        => 'po no ' || ' : ' || '91300100187',
                        body           => 'body',
                        errormessage   =>  errormessage,
                        attachments    => null
                      );
Exception
When others then
 dbms_output.put_line('in exception :  '||sqlerrm || errormessage);	
 dbms_output.put_line('in exception :  '|| error_status);				  
End;