SQL & PL/SQL

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