1 Reply Latest reply: Mar 19, 2013 10:42 AM by rukbat RSS

    Problem adding attachments to email

    996134
      Hi,

      I'm using the java classes and PL/SQL package found here (http://www.akadia.com/services/java_mail_plsql.html) to send emails from within oracle. Text only emails are working fine but I get the following error when trying to add an attachement.

      PL/SQL procedure successfully completed.
      ORA-06502: PL/SQL: numeric or value error
      ORA-06512: at "UMSDATA.SENDMAILJPKG", line 12
      ORA-06512: at "UMSDATA.SENDMAILJPKG", line 44
      ORA-06512: at line 2


      Here is the code I'm using to send the email and the package body:
      var ErrorMessage VARCHAR2(4000);
      var ErrorStatus NUMBER;
      
      -- enable SQL*PLUS output;
      SET SERVEROUTPUT ON
      -- redirect java output into SQL*PLUS buffer;
      exec dbms_java.set_output(5000);
      BEGIN
         :ErrorStatus := SendMailJPkg.SendMail(
                      SMTPServerName => 'server',
                      Sender    => 'email@email.com',
                      Recipient => 'email@email.com',
                      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('/home/oracle/pdfs/Steps.txt') 
         );
      END;
      /
      print
      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;
      /
      Thank you,
      Duane