5 Replies Latest reply: Mar 22, 2013 12:07 AM by Billy~Verreynne RSS

    utl_smtp with richtext message bodies

    576957
      Hi All,

      I am working on a new process to send emails with attachments (PDF's), out of Oracle using utl_smtp. I've done a lot of research on the web on this and have found many code examples which I have used and modified for my needs. I have been able to successfully send emails with multiple attachments but now I am dealing with one more issue that I have not been able to figure out yet. I am under a very tight deadline to get this completed so I am asking for your help in finding a solution to my issue.

      We have an application that is written in PowerBuilder 12.5 that has some custom email functionality which allows the users to either write an email message from scratch or use a pre-existing email message template which they modify for their needs. Once they finish writing the email message they click a button to save the email information to some Oracle tables. The control that is used to create these email messages is a richtext edit control. It allows the users to use different fonts, highlighting, bolding and so on, just like they can do in Outlook. This richtext is then saved in a BLOB column in one of the Oracle tables.

      This is my issue. I need to read that BLOB with the richtext and insert the text into the email message body. I have not been able to figure this out yet. Everything I have tried so far has just resulted in either the text showing up in the body of the email as a bunch of un-readable garbage or as an attachment that is also un-readable. I did some research on MIME file types and I believe I need to use either Content-Type: application/rtf or Content-Type: text/richtext.

      This is the code that I am using to generate the email message body:

      -- Mail Body
      UTL_SMTP.WRITE_DATA(gv_smtp_conn, '--' || 'RJIG.SECBOUND' || UTL_TCP.CRLF);
      UTL_SMTP.WRITE_DATA(gv_smtp_conn, 'Content-Type: application/rtf;' || UTL_TCP.CRLF);
      --     'Content-Type: text/richtext;' || UTL_TCP.CRLF);
      UTL_SMTP.WRITE_DATA(gv_smtp_conn, ' charset=US-ASCII' || UTL_TCP.CRLF);
      UTL_SMTP.WRITE_DATA(gv_smtp_conn, UTL_TCP.CRLF);

      /* initializing the varriables. */
      l_buffer := NULL;
      l_offset := 1;
      l_amount := 3456;
      /* Writing the BLOB in chunks */
      l_blob_len := DBMS_LOB.getlength(p_body); -- p_body is the blob containing the richtext data
      WHILE l_offset < l_blob_len LOOP
      DBMS_LOB.READ(p_body, l_amount, l_offset, l_buffer);
      UTL_SMTP.WRITE_RAW_DATA(gv_smtp_conn, UTL_ENCODE.BASE64_ENCODE(l_buffer));
      UTL_SMTP.WRITE_DATA(gv_smtp_conn, UTL_TCP.CRLF);
      l_buffer := NULL;
      l_offset := l_offset + l_amount;
                l_amount := LEAST(l_amount, DBMS_LOB.getlength(p_body) - l_amount);
      END LOOP;
      UTL_SMTP.WRITE_DATA(gv_smtp_conn, UTL_TCP.CRLF);

      This code above is basically the same code that I am using to attach the PDF's to the email message which works great. I have a feeling that my issue has something to do with the charset I am using or possibly encoding/decoding of the data from the blob. I just want this richtext data to appear in the email message body and not as an attachment.

      Any help with this would be very much appreciated. I've spent two days trying to figure this out already and I am at my wits end.

      Thank you,
      Guy
        • 1. Re: utl_smtp with richtext message bodies
          Stefan Jager
          Hi Guy,

          This isn't a PL/SQL or SQL question, but about how an email is defined. You are adding the BAS64-encoded blob as email body, so that won't look good. I've forgotten exactly how it works, but you need to specify a multi-part email, with the format specifying RTF as mime-type. That should enable most email clients to display the email correctly. Maybe have a look at [url http://tools.ietf.org/html/rfc2822]RFC 2822 and it's relatives.

          HTH,
          Stefan
          • 2. Re: utl_smtp with richtext message bodies
            damorgan
            Based on what you've posted it is hard to provide any help. We don't know your Oracle version number, you allude to a possible character set issue but don't tell us what your current character set is (or any other NLS settings that might be helpful), but there should be little difference in send one blob (PDF) and another (RICHTEXT) so my thought would be to use a good byte editor to examine both side-by-side and figure out what is changed such that the one sent is not readable.
            • 3. Re: utl_smtp with richtext message bodies
              576957
              I'm sorry for omitting that information. I am using Oracle 11.2.0.2.

              This is all PL/SQL code writtten in a stored procedure.

              The character set I was referring to was in the code sample I provided above, UTL_SMTP.WRITE_DATA(gv_smtp_conn, ' charset=US-ASCII' || UTL_TCP.CRLF);. I am in the USA.

              The issue is that I don't want the richtext to be sent as an attachment like the PDF files are. I want it to show up in the message body but what I end up getting is garbage like this:

              ewBcAHIAdABmADEAXABhAG4AcwBpAFwAYQBuAHMAaQBjAHAAZwAxADIANQAyAFwA
              dQBjADEAXABkAGUAZgBmADAAewBcAGYAbwBuAHQAdABiAGwADQAKAHsAXABmADAA
              XABmAG4AaQBsAFwAZgBjAGgAYQByAHMAZQB0ADAAXABmAHAAcgBxADIAIABBAHIA
              aQBhAGwAOwB9AA0ACgB7AFwAZgAxAFwAZgBuAGkAbABcAGYAYwBoAGEAcgBzAGUA
              dAAwAFwAZgBwAHIAcQAwACAAVABpAG0AZQBzACAATgBlAHcAIABSAG8AbQBhAG4A
              OwB9AA0ACgB7AFwAZgAyAFwAZgBuAGkAbABcAGYAYwBoAGEAcgBzAGUAdAAwAFwA
              ZgBwAHIAcQAwACAAQwBhAGwAaQBiAHIAaQA7AH0ADQAKAHsAXABmADMAXABmAHMA

              So I'm thinking it has something to do with how I am reading and writing the blob data.

              DBMS_LOB.READ(p_body, l_amount, l_offset, l_buffer);
              UTL_SMTP.WRITE_RAW_DATA(gv_smtp_conn, UTL_ENCODE.BASE64_ENCODE(l_buffer));
              UTL_SMTP.WRITE_DATA(gv_smtp_conn, UTL_TCP.CRLF);

              Or maybe it has something to do with the MIME headers I am using for the message body. Before I read the blob's for the attachments, the PDF's, I execute the following code:

              -- Mail Attachment
              UTL_SMTP.WRITE_DATA(gv_smtp_conn, '--' || 'RJIG.SECBOUND' || UTL_TCP.CRLF);
              UTL_SMTP.WRITE_DATA(gv_smtp_conn, 'Content-Type: application/octet-stream' || UTL_TCP.CRLF);
              UTL_SMTP.WRITE_DATA(gv_smtp_conn, 'Content-Disposition: attachment; ' || UTL_TCP.CRLF);
              UTL_SMTP.WRITE_DATA(gv_smtp_conn, ' filename="' || l_fname || '"' || UTL_TCP.CRLF);
              UTL_SMTP.WRITE_DATA(gv_smtp_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
              UTL_SMTP.WRITE_DATA(gv_smtp_conn, UTL_TCP.CRLF);

              I'm just at loss here as I've tried so many different things and nothing has worked yet.
              • 4. Re: utl_smtp with richtext message bodies
                Stefan Jager
                Hi Guy,
                garbage like this
                What you are seeing there is the Base64 encoded RTF Blob.
                it has something to do with how I am reading and writing the blob data.
                No.
                it has something to do with the MIME headers I am using for the message body
                Partly. As I said before, you need to set up a Multipart email message. I've found a bit better description of that format at W3C: [url http://www.w3.org/Protocols/rfc1341/7_2_Multipart.html]The Multipart Content-Type.

                So what your headers and body should look like is something like this:
                From:  Somebody <somebody@mail.com> 
                To: Someone Else <someone.else@mail.com> 
                Subject: Formatted text mail 
                MIME-Version: 1.0 
                Content-Type: multipart/alternative; boundary=boundary42 
                
                
                --boundary42 
                Content-Type: text/plain; charset=us-ascii 
                
                ...plain text version of message goes here.... 
                
                --boundary42 
                Content-Type: text/richtext 
                
                .... richtext version of same message goes here ... 
                Be aware that not all mail clients support RTF messages properly, which is why you should add plain text content as well.

                I am not familiar with the UTL_SMTP package, so how you would exactly set that up in code I don't know, but the email should look like the above, and then it should work.

                HTH,
                Stefan
                • 5. Re: utl_smtp with richtext message bodies
                  Billy~Verreynne
                  Binary data needs to be base64 encoded. The attachment needs to have the correct Mime header and boundary tags.

                  For base64 encoding, see {message:id=10364104}.

                  As for richtext - I'm not sure that this can be natively rendered by a mail reader. It is also NOT a binary format. It needs and uses 7 bit ASCII only, as far as I know. It supports character and page escape codes - with character escapes providing Unicode support.

                  My guess is that your loading of RTF files as BLOBs and then converting that to base64, is the cause of the mail attachment format problem. It should be loaded as CLOB instead. No base64 encoding needed. (unsure how exactly RTF supports embedded bindary objects like images - never looked at RTF that closely, and would not use it for a document exchange format)