SQL & PL/SQL

Hello,

I've been successfully using the Demo_Mail package with the example at http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/Utl_Smtp_Sample.html for many years on my 9i database.

Recently, we've taken a form that depends upon this package global and quickly found that asian characters were not displaying properly in the email subject or body. When I modified the procedure per the example, the special characters came through, however, the attachments now appear within the email body as ascii.

/*------------------------------------------------------start the email creation--------------------------------------------------------------------*/
conn := demo_mail.begin_mail(
sender => from_name,
recipient => to_name,
subject => subject,
mime_type => 'text/plain; charset=utf8');

demo_mail.write_mb_text(
conn => conn,
message => message || utl_tcp.CRLF);

if filename1 is not null
then
demo_mail2.begin_attachment(
conn => conn,
mime_type => 'application/octet-stream',
inline => FALSE,
v_filename => filename1,
transfer_enc => 'base64');

file_len := dbms_lob.getlength(fil);
modulo := mod(file_len, amt);
pieces := trunc(file_len / amt);


/*(dbms_lob.read(fil, amt, filepos, buf); */


while (counter <= pieces) LOOP

dbms_lob.read(fil, amt, filepos, buf);
demo_mail.write_raw(conn, utl_encode.base64_encode(buf));
filepos := counter * amt + 1;
counter := counter + 1;

END LOOP;

/* Point at the rest of the data buffer */
IF (modulo <> 0) then
dbms_lob.read(fil, modulo, filepos, buf);
demo_mail2.write_raw(conn, utl_encode.base64_encode(buf));
end if;

demo_mail.end_attachment(
conn => conn);
end if;

demo_mail.end_mail(
conn => conn);
/*------------------------------------------------------end the email creation--------------------------------------------------------------------*/


How can I have my cake and eat it too? Do any sample scripts showing this scenario exist?

In addition, even though I've been able to get the body of the email to display special characters, the subject line will still display question marks in place of the special characters. I've never been able to resolve that issue.

Any assistance with either of these issues is greatly appreciated!

Thank you,

Kim