This discussion is archived
14 Replies Latest reply: Nov 22, 2010 11:46 PM by egudai RSS

Issue with Demo_Mail package sending both attachment and special characters

khenders Newbie
Currently Being Moderated
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
  • 1. Re: Issue with Demo_Mail package sending both attachment and special characters
    Sentinel Pro
    Currently Being Moderated
    Kim,

    You should be able to utl_encode.MIMEHEADER_ENCODE(subject); your subject when passing it to the demo_mail.begin_mail procedure. This function will specifiy what characterset your header is in and encode it appropriately.
  • 2. Re: Issue with Demo_Mail package sending both attachment and special characters
    khenders Newbie
    Currently Being Moderated
    Thank you, Sentinel-

    So I've updated my procedure to read as follows:

    conn := demo_mail.begin_mail(
    sender => from_name,
    recipient => to_name,
    subject => utl_encode.MIMEHEADER_ENCODE(subject),
    mime_type => 'text/plain; charset=utf8');

    However, now it gives me an error stating that I need to declare MIMEHEADER_ENCODE.

    Line # = 212 Column # = 30 Error Text = PLS-00302: component 'MIMEHEADER_ENCODE' must be declared

    How should I declare it?

    Thanks!

    Kim
  • 3. Re: Issue with Demo_Mail package sending both attachment and special characters
    Sentinel Pro
    Currently Being Moderated
    Ok I just double checked the documentation for 9i vs 10g, and the mimehead_encode function is not a part of the utl_encode package on 9i. I guess it's a 10g+ feature only.

    However, the output of the mimehead_encode function generally takes the following form:
    =?<character_set>?<encoding>?<encoded_text>?=
    where chracter_set is whichever charcacter set you are using e.g. UTF-8, encoding indicates the algorithm used to encode the text e.g. Q for a modified version of quoted printable and B indicates base64 encoding and encoded_text is of course the encoded text.

    You can see RFC2047| for more info on this encoding.
    According to RFC2047:
    Only a subset of the printable ASCII characters may be used in
    'encoded-text'. Space and tab characters are not allowed, so that
    the beginning and end of an 'encoded-word' are obvious. The "?"
    character is used within an 'encoded-word' to separate the various
    portions of the 'encoded-word' from one another, and thus cannot
    appear in the 'encoded-text' portion. Other characters are also
    illegal in certain contexts. For example, an 'encoded-word' in a
    'phrase' preceding an address in a From header field may not contain
    any of the "specials" defined in RFC 822. Finally, certain other
    characters are disallowed in some contexts, to ensure reliability for
    messages that pass through internetwork mail gateways.>

    Due to the difference between normal quoted printable encoding and this modified version you may be better off generating a base64 encoded version of your subject:
    subject:=utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(subject)));
    subject:='=?UTF-8?B?'||subject||'?='
  • 4. Re: Issue with Demo_Mail package sending both attachment and special characters
    khenders Newbie
    Currently Being Moderated
    Thanks again for your help, Sentinel-

    I've updated my procedure so it now reads as follows:

    CREATE OR REPLACE PROCEDURE
    (to_name in varchar2 default null,
    from_name in varchar2 default null,
    subject in varchar2 default null,
    message in varchar2 default null)
    as

    t_subject varchar2(200);
    s_subject varchar2(200;

    BEGIN
    DECLARE
    cursor F is (select MAX(id) from log);
    f_ans number;

    BEGIN

    open F;
    fetch F into f_ans;

    t_subject:=f_ans||' '||subject;

    if v_region='JAPAC'
    or v_region='LAD'
    or v_region='EMEA'
    then
    s_subject:=utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(t_subject)));
    s_subject:='=?UTF8?B?'||s_subject||'?=';

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

    demo_mail.write_mb_text(
    conn => conn,
    message => message);

    if filename1 is not null
    then
    demo_mail.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_mail.write_raw(conn, utl_encode.base64_encode(buf));
    end if;

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

    demo_mail.end_mail(
    conn => conn);
    /*------------------------------------------------------------Email Sent----------------------------------------------------------*/


    end if;
    end;
    end;

    and a clip of the email I receive looks like this:


    Subject: =?UTF8?B?MTA5MjUgQ29sb21iaWEtIENyZWRpdCBDaGVjayBSZXF1ZXN0IGZvciBhw7FvLzcv
    From: Me
    Date: 5:36pm
    To: someaddress@oracle.com

    MjAtb2N0LTA4?=
    MIME-Version: 1.0
    Content-Type: text/plain; charset=utf8
    X-Mailer: Mailer by Oracle UTL_SMTP

    Please review and approve the following credit request:

    Region: Some region
    Country: Some country

    Bill To Company Name: año


    The Bill To Company Name is not displaying correctly either as it should display as año and it is displaying as año. Any ideas?

    Thanks!

    Kim
  • 5. Re: Issue with Demo_Mail package sending both attachment and special characters
    Sentinel Pro
    Currently Being Moderated
    Hi Kim,

    Just to double check, when you state:
    khenders wrote:
    ...
    and a clip of the email I receive looks like this:


    Subject: =?UTF8?B?MTA5MjUgQ29sb21iaWEtIENyZWRpdCBDaGVjayBSZXF1ZXN0IGZvciBhw7FvLzcv
    From: Me
    Date: 5:36pm
    To: someaddress@oracle.com

    MjAtb2N0LTA4?=
    MIME-Version: 1.0
    Content-Type: text/plain; charset=utf8
    X-Mailer: Mailer by Oracle UTL_SMTP

    Please review and approve the following credit request:

    Region: Some region
    Country: Some country

    Bill To Company Name: año


    The Bill To Company Name is not displaying correctly either as it should display as año and it is displaying as año. Any ideas?
    Are you referring to text in the body of the email that is not displaying correctly?

    If that is the case, then you may be running into the fact that SMTP, unless otherwise directed, expects your email to be encoded in 7bit ASCII. By using the write_mb_text function demo mail is casting your text to raw and then writing raw data to the SMTP server, including high order ASCII characters. For instance ñ is represented as the following two byte sequence (decimal representation) 195, 177, both of which have the most significant bit set (> 127). Since the SMTP server has not been instructed to expect 8bit ASCII or any other encoding it's may be stripping the high order bits or otherwise missinterpreting the content.

    You should be able to get around this by changing the content transfer encodeing to something more appropriate to your purposes.

    Now you can't set the content transfer ecoding header when you open your mail message with the begin_mail function, but instead of creating a text/plain message, you could make a multipart/mixed message and then include your message as the first inline attachment as demonstrated by this code snippet:
    ...
    conn := demo_mail.begin_mail(
            sender => from_name,
            recipient => to_name,
            subject => s_subject,
    --"Begin Changes"
            mime_type => demo_mail.MULTIPART_MIME_TYPE);
    
    demo_mail.write_text(
            conn => conn,
            message => 'This is a multi-part message in MIME format.' ||
                utl_tcp.crlf);
    
    demo_mail.begin_attachment(
            conn => conn,
            mime_type => 'text/plain; charset=utf8',
            transfer_enc => '8bit');
    
    demo_mail.write_mb_text(
            conn => conn,
            message => message);
    
    demo_mail.end_attachment(conn => conn);
    --"End Changes"
    
    if filename1 is not null then 
    ...
    Note: I have not tested this particular code snippet, but expect it should work.

    By not providing a filename to the begin_attachment procedure, this code is telling demo_mail to begin a new mime content section instead of an attached file. The call to write_text between the call to begin_mail and the call begin_attachment typicaly would not be displayed to recipients unless they are using a non mime compliant mail reader, in which case it's considerate to inform them that the content is mime encoded
  • 6. Re: Issue with Demo_Mail package sending both attachment and special characters
    khenders Newbie
    Currently Being Moderated
    Thanks, Sentinel-

    Once again, you have been very helpful! I now have the template successfully submitting an email that will display special characters AND attach files to the email. The only outstanding issue is getting that subject line to recognize local country characters. I've tried your solution above, and all I ever get is a long string of alphanumeric characters in the subject line like this:

    Subject: =?UTF8?B?MTA5MjUgQ29sb21iaWEtIENyZWRpdCBDaGVjayBSZXF1ZXN0IGZvciBhw7FvLzcv

    This is what I am using in my code:

    t_subject:=f_ans||' '||subject;

    if v_region='JAPAC'
    or v_region='LAD'
    or v_region='EMEA'
    then
    s_subject:=utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(t_subject)));
    s_subject:='=?UTF8?B?'||s_subject||'?=';

    conn := demo_mail.begin_mail(
    sender => from_name,
    recipient => to_name,
    subject => s_subject,
    mime_type => demo_mail.MULTIPART_MIME_TYPE);

    demo_mail.write_text(
    conn => conn,
    message => 'This is a multi-part message in MIME format.' ||
    utl_tcp.crlf);

    demo_mail.begin_attachment(
    conn => conn,
    mime_type => 'text/plain; charset=utf8',
    transfer_enc => '8bit');

    demo_mail.write_mb_text(
    conn => conn,
    message => message);

    demo_mail.end_attachment(conn => conn);

    if filename1 is not null...


    The t_subject variable takes the subject variable that is put together using javascript from a previous page and passed to this procedure and adds a sequential number (i.e., f_ans) to it. If I create a new variable, s_subject, and then have the t_subject cast to raw and encoded then cast to varchar2, I still get that long alphanumeric string for the subject line. I've tried removing the "s_subject='=?UTF8?B?'||s_subject||'?='" but that doesn't work either.

    Thanks again for helping me so much! Your generosity is greatly appreciated.

    Kim
  • 7. Re: Issue with Demo_Mail package sending both attachment and special characters
    khenders Newbie
    Currently Being Moderated
    Can anyone help with the issue of getting local country characters to display in the subject line of an email when using the demo_mail package? I've tried the following code suggestion to no avail:

    s_subject:=utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(t_subject)));
    s_subject:='=?UTF8?B?'||s_subject||'?=';

    I simply get a long string of alphanumeric characters in my subject line when using that code. Does some other conversion need to take place to put it into readable format?

    Thanks!

    Kim
  • 8. Re: Issue with Demo_Mail package sending both attachment and special characters
    Sentinel Pro
    Currently Being Moderated
    Well Kim since the base64 encoding isn't working for you perhaps trying to emulate the modified quoted printable encoding would work:

    Start by converting your string to quoted printable format
    s_subject:=utl_raw.cast_to_varchar2(utl_encode.quoted_printable_encode(utl_raw.cast_to_raw(t_subject)));
    then convert the characters that shouldn't be included in the clear to their quoted printable form
    s_subject:=replace(s_subject,'?','=3f'); --quote question marks
    s_subject:=replace(s_subject,' ','=20'); --quote spaces
    s_subject:=replace(s_subject,chr(10),''); --remove line feeds
    -- Possibly others as well
    then embed it in the appropriate tags:
    s_subject:='=?UTF-8?Q?'||s_subject||'?=';
  • 9. Re: Issue with Demo_Mail package sending both attachment and special characters
    khenders Newbie
    Currently Being Moderated
    Thanks again, Sentinel-

    I'm still not getting a good subject. Below is the type of Subject line I receive in the email that is generated:

    =?UTF-8?Q?17876=20Portugal-=20Credit=20Check=20Request=20for=20=C3=A7=C3=A3o/5/20-Nov=

    If I remove the s_subject:='=?UTF-8?Q?'||s_subject||'?='; code then the subject line looks like this:

    17877=20Portugal-=20Credit=20Check=20Request=20for=20=C3=A7=C3=A3o/4/20-Nov=


    This is how I modified my procedure based upon your recommendation:

    elsif v_region='JAPAC'
    or v_region='LAD'
    or v_region='EMEA'
    then
    s_subject:=utl_raw.cast_to_varchar2(utl_encode.quoted_printable_encode(utl_raw.cast_to_raw(t_subject)));
    s_subject:=replace(s_subject,'?','=3f'); --quote question marks
    s_subject:=replace(s_subject,' ','=20'); --quote spaces
    s_subject:=replace(s_subject,chr(10),''); --remove line feeds
    s_subject:=replace(s_subject,'¿','=BF');
    s_subject:=replace(s_subject,'À','=C0');
    s_subject:=replace(s_subject,'Á','=C1');
    s_subject:=replace(s_subject,'Â','=C2');
    s_subject:=replace(s_subject,'Ã','=C3');
    s_subject:=replace(s_subject,'Ä','=C4');
    s_subject:=replace(s_subject,'Å','=C5');
    s_subject:=replace(s_subject,'Æ','=C6');
    s_subject:=replace(s_subject,'Ç','=C7');
    s_subject:=replace(s_subject,'È','=C8');
    s_subject:=replace(s_subject,'É','=C9');
    s_subject:=replace(s_subject,'Ê','=CA');
    s_subject:=replace(s_subject,'Ë','=CB');
    s_subject:=replace(s_subject,'Ì','=CC');
    s_subject:=replace(s_subject,'Í','=CD');
    s_subject:=replace(s_subject,'Î','=CE');
    s_subject:=replace(s_subject,'Ï','=CF');
    s_subject:=replace(s_subject,'Ð','=D0');
    s_subject:=replace(s_subject,'Ñ','=D1');
    s_subject:=replace(s_subject,'Ò','=D2');
    s_subject:=replace(s_subject,'Ó','=D3');
    s_subject:=replace(s_subject,'Ô','=D4');
    s_subject:=replace(s_subject,'Õ','=D5');
    s_subject:=replace(s_subject,'Ö','=D6');
    s_subject:=replace(s_subject,'Ù','=D9');
    s_subject:=replace(s_subject,'Ú','=DA');
    s_subject:=replace(s_subject,'Û','=DB');
    s_subject:=replace(s_subject,'Ü','=DC');
    s_subject:=replace(s_subject,'Ý','=DD');
    s_subject:=replace(s_subject,'Þ','=DE');
    s_subject:=replace(s_subject,'ß','=DF');
    s_subject:=replace(s_subject,'à','=E0');
    s_subject:=replace(s_subject,'á','=E1');
    s_subject:=replace(s_subject,'â','=E2');
    s_subject:=replace(s_subject,'ã','=E3');
    s_subject:=replace(s_subject,'ä','=E4');
    s_subject:=replace(s_subject,'å','=E5');
    s_subject:=replace(s_subject,'æ','=E6');
    s_subject:=replace(s_subject,'ç','=E7');
    s_subject:=replace(s_subject,'è','=E8');
    s_subject:=replace(s_subject,'é','=E9');
    s_subject:=replace(s_subject,'ê','=EA');
    s_subject:=replace(s_subject,'ë','=EB');
    s_subject:=replace(s_subject,'ì','=EC');
    s_subject:=replace(s_subject,'í','=ED');
    s_subject:=replace(s_subject,'î','=EE');
    s_subject:=replace(s_subject,'ï','=EF');
    s_subject:=replace(s_subject,'ð','=F0');
    s_subject:=replace(s_subject,'ñ','=F1');
    s_subject:=replace(s_subject,'ò','=F2');
    s_subject:=replace(s_subject,'ó','=F3');
    s_subject:=replace(s_subject,'ô','=F4');
    s_subject:=replace(s_subject,'õ','=F5');
    s_subject:=replace(s_subject,'ö','=F6');
    s_subject:=replace(s_subject,'ù','=F9');
    s_subject:=replace(s_subject,'ú','=FA');
    s_subject:=replace(s_subject,'û','=FB');
    s_subject:=replace(s_subject,'ü','=FC');
    s_subject:=replace(s_subject,'ý','=FD');
    s_subject:=replace(s_subject,'þ','=FE');
    s_subject:=replace(s_subject,'ÿ','=FF');
    /*s_subject:='=?UTF-8?Q?'||s_subject||'?=';*/


    /*------------------------------------------------------start the email
    creation--------------------------------------------------------------------*/
    conn := demo_mail.begin_mail(
    sender => from_name,
    recipient => to_name,
    subject => s_subject,
    mime_type => demo_mail.MULTIPART_MIME_TYPE);


    demo_mail.write_text(
    conn => conn,
    message => 'This is a multi-part message in MIME format.' ||
    utl_tcp.crlf);

    demo_mail.begin_attachment(
    conn => conn,
    mime_type => 'text/plain; charset=utf8',
    transfer_enc => 'quoted-printable');

    demo_mail.write_mb_text(
    conn => conn,
    message => message);

    demo_mail.end_attachment(conn => conn);

    In addition, I'm concerned that using this path would mean that all European and Latin characters would be displayed properly, but I would still have an issue with arabic or asian characters, correct?

    Thanks again!

    Kim
  • 10. Re: Issue with Demo_Mail package sending both attachment and special characters
    Sentinel Pro
    Currently Being Moderated
    Are you using the utl_encode.quoted_printable_encode before doing all those replaces? Most if not all of those accented characters should be converted to the quoted printable form by the utl_encode.quoted_printable_encode function, then you only need to focus on the specific characters that should be encoded for the string to meet the additional requirements for mimeheader encoding. Specifically white space characters should be encoded as well as the question mark.

    Also I did some minor testing and it looks like the quoted_printable_encode function is folding the text when it exceeds some specific length. If I unfold the encoded text subject lines seem to come through fine. The text folding string is an equals sign folowed by the CRLF sequence, so unfolding the text is a simple matter of stripping out those three characters. With these changes the encoding routine encapsulated in a function becomes:
    create or replace function mimeheader_encode(
          p_str varchar2
        , p_charset varchar2 := 'UTF-8') return varchar2 is
      l_str varchar2(2000);
    begin
      l_str:=utl_raw.cast_to_varchar2(utl_encode.quoted_printable_encode(utl_raw.cast_to_raw(p_str)));
      l_str:=replace(l_str,'='||chr(13)||chr(10),''); --unfold the data
      l_str:=replace(l_str,'?','=3f'); --quote question marks
      l_str:=replace(l_str,' ','=20'); --quote spaces
      l_str:='=?'||p_charset||'?Q?'||l_str||'?='; -- add prefix and suffix
      return l_str;
    end;
    /
  • 11. Re: Issue with Demo_Mail package sending both attachment and special characters
    khenders Newbie
    Currently Being Moderated
    Sentinel-

    You're a genius! I took the code out of my procedure and used your function and now it is working great! Thank you so much for sticking it out with me to the very end. I really appreciate it!

    Kim
  • 12. Re: Issue with Demo_Mail package sending both attachment and special characters
    747113 Newbie
    Currently Being Moderated
    This thread has been really helpful, but I have a bit of a problem with the email subject line.

    I have set up an email process based on demo mail, which works well sending all types of emails, with and without attachments. I now have to enable the email process to send email with accented and non-English characters. I have used the function in this thread to send simple subject lines, although I did find I had to base64 encode these, and had to use iso-8859-1 or iso-8859-15, rather than utf-8.

    I am trying to send long subject lines, but as there is a restriction on the number of characters in the string of =?<charset>?B?<encoded string>?=, I not sure how to join multiple strings. Has anyone come across this before, and if so, how do you solve it?

    Hoping somebody can help me.

    Thanks,

    Paul
  • 13. Re: Issue with Demo_Mail package sending both attachment and special characters
    egudai Newbie
    Currently Being Moderated
    Hello To you all,

    Im trying to figure out the exact implementation as I am running to the same issue with Hebrew.
    It would be much appreciated if you can attach the package here , or send it directly to etay.gudai@gmail.com
    so I can follow up the exact code & save the trial and error part.

    Thanks a lot.
  • 14. Re: Issue with Demo_Mail package sending both attachment and special characters
    egudai Newbie
    Currently Being Moderated
    Hello All,

    Well ...succeeded to implement no need to reply :

    HERE IS A WORKING CODE FOR SENDING HEBREW MESSAGES (INCLUDING SUBJECT IN UTF-8 APPEAR IN ALL EMAIL CLIENTS I HAVE CHECKED) + ATTACHMENTS

    Code attached below is supplied as is with no support. anyhow if help is needed , please contact me via etay.gudai@gmail.com

    ============================================================================

    CREATE OR REPLACE PACKAGE demo_mail_heb IS

    ----------------------- Customizable Section -----------------------

    -- Customize the SMTP host, port and your domain name below.
    smtp_host VARCHAR2(256) := pst_ajax.getParameter('EMAIL_SMTP_HOST');
    smtp_port PLS_INTEGER := pst_ajax.getParameter('EMAIL_SMTP_PORT');
    smtp_domain VARCHAR2(256) := pst_ajax.getParameter('EMAIL_SMTP_DOMAIN');

    -- Customize the signature that will appear in the email's MIME header.
    -- Useful for versioning.
    MAILER_ID CONSTANT VARCHAR2(256) := 'Mailer by Oracle UTL_SMTP';

    --------------------- End Customizable Section ---------------------

    -- A unique string that demarcates boundaries of parts in a multi-part email
    -- The string should not appear inside the body of any part of the email.
    -- Customize this if needed or generate this randomly dynamically.
    BOUNDARY CONSTANT VARCHAR2(256) := '-----7D81B75CCC90D2974F7A1CBD';

    FIRST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || utl_tcp.CRLF;
    LAST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || '--' ||
    utl_tcp.CRLF;

    -- A MIME type that denotes multi-part email (MIME) messages.
    MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="'||
    BOUNDARY || '"';
    MAX_BASE64_LINE_WIDTH CONSTANT PLS_INTEGER := 76 / 4 * 3;


    ------------------------
    -- Sent clear Html Email
    -------------------------
    procedure send_html_mail (p_sender in varchar2 default null,
    p_recipients in varchar2 default null,
    p_subject in varchar2 default null,
    p_data in varchar2 default null,
    p_mime_type in varchar2 default 'text/html; charset=windows-1255');

    -----------------------------------
    -- Sent Html Email with Attachment
    -----------------------------------
    procedure send_html_mail_attach (p_sender in varchar2 default null,
    p_recipients in varchar2 default null,
    p_subject in varchar2 default null,
    p_data in varchar2 default '<b>áå÷ø èåá òåìí - áãé÷ä</b',
    p_mime_type in varchar2 default 'text/html; charset=windows-1255',
    p_file_name in varchar2 default 'but_choose_file.gif',
    p_file_mime_type in varchar2 default 'application/pdf',
    p_file_URL in varchar2 default 'http://10.172.246.160:7777/i/but_choose_file.gif');

    -- A simple email API for sending email in plain text in a single call.
    -- The format of an email address is one of these:
    -- someone@some-domain
    -- "Someone at some domain" <someone@some-domain>
    -- Someone at some domain <someone@some-domain>
    -- The recipients is a list of email addresses separated by
    -- either a "," or a ";"
    PROCEDURE mail(sender IN VARCHAR2,
              recipients IN VARCHAR2,
              subject IN VARCHAR2,
              message IN VARCHAR2);

    -- Extended email API to send email in HTML or plain text with no size limit.
    -- First, begin the email by begin_mail(). Then, call write_text() repeatedly
    -- to send email in ASCII piece-by-piece. Or, call write_mb_text() to send
    -- email in non-ASCII or multi-byte character set. End the email with
    -- end_mail().
    FUNCTION begin_mail(sender IN VARCHAR2,
              recipients IN VARCHAR2,
              subject IN VARCHAR2,
              mime_type IN VARCHAR2 DEFAULT 'text/plain',
              priority IN PLS_INTEGER DEFAULT NULL)
              RETURN utl_smtp.connection;

    -- Write email body in ASCII
    PROCEDURE write_text(conn IN OUT NOCOPY utl_smtp.connection,
              message IN VARCHAR2);

    -- Write email body in non-ASCII (including multi-byte). The email body
    -- will be sent in the database character set.
    PROCEDURE write_mb_text(conn IN OUT NOCOPY utl_smtp.connection,
                   message IN VARCHAR2);

    -- Write email body in binary
    PROCEDURE write_raw(conn IN OUT NOCOPY utl_smtp.connection,
              message IN RAW);

    -- APIs to send email with attachments. Attachments are sent by sending
    -- emails in "multipart/mixed" MIME format. Specify that MIME format when
    -- beginning an email with begin_mail().

    -- Send a single text attachment.
    PROCEDURE attach_text(conn IN OUT NOCOPY utl_smtp.connection,
                   data IN VARCHAR2,
                   mime_type IN VARCHAR2 DEFAULT 'text/plain',
                   inline IN BOOLEAN DEFAULT TRUE,
                   filename IN VARCHAR2 DEFAULT NULL,
              last IN BOOLEAN DEFAULT FALSE);

    -- Send a binary attachment. The attachment will be encoded in Base-64
    -- encoding format.
    PROCEDURE attach_base64(conn IN OUT NOCOPY utl_smtp.connection,
                   data IN RAW,
                   mime_type IN VARCHAR2 DEFAULT 'application/octet',
                   inline IN BOOLEAN DEFAULT TRUE,
                   filename IN VARCHAR2 DEFAULT NULL,
                   last IN BOOLEAN DEFAULT FALSE);

    -- Send an attachment with no size limit. First, begin the attachment
    -- with begin_attachment(). Then, call write_text repeatedly to send
    -- the attachment piece-by-piece. If the attachment is text-based but
    -- in non-ASCII or multi-byte character set, use write_mb_text() instead.
    -- To send binary attachment, the binary content should first be
    -- encoded in Base-64 encoding format using the demo package for 8i,
    -- or the native one in 9i. End the attachment with end_attachment.
    PROCEDURE begin_attachment(conn IN OUT NOCOPY utl_smtp.connection,
                   mime_type IN VARCHAR2 DEFAULT 'text/plain',
                   inline IN BOOLEAN DEFAULT TRUE,
                   filename IN VARCHAR2 DEFAULT NULL,
                   transfer_enc IN VARCHAR2 DEFAULT NULL);

    -- End the attachment.
    PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,
                   last IN BOOLEAN DEFAULT FALSE);

    -- End the email.
    PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection);

    -- Extended email API to send multiple emails in a session for better
    -- performance. First, begin an email session with begin_session.
    -- Then, begin each email with a session by calling begin_mail_in_session
    -- instead of begin_mail. End the email with end_mail_in_session instead
    -- of end_mail. End the email session by end_session.
    FUNCTION begin_session RETURN utl_smtp.connection;

    ---------------------
    -- Handling the Email Subject Line
    ---------------------
    function mimeheader_encode(
    p_str varchar2
    , p_charset varchar2 := 'UTF-8') return varchar2;

    -- Begin an email in a session.
    PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,
                        sender IN VARCHAR2,
                        recipients IN VARCHAR2,
                        subject IN VARCHAR2,
    --                     mime_type IN VARCHAR2 DEFAULT 'text/plain; charset=windows-1255',
              mime_type IN VARCHAR2 DEFAULT 'text/plain',
                        priority IN PLS_INTEGER DEFAULT NULL);

    -- End an email in a session.
    PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection);

    -- End an email session.
    PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection);

    END;


    CREATE OR REPLACE PACKAGE BODY demo_mail_heb IS


    ------------------------
    -- Sent clear Html Email
    -------------------------
    procedure send_html_mail (p_sender in varchar2 default null,
    p_recipients in varchar2 default null,
    p_subject in varchar2 default null,
    p_data in varchar2 default null,
    p_mime_type in varchar2 default 'text/html; charset=windows-1255')
    Is
    conn utl_smtp.connection;
    BEGIN
    conn := demo_mail_heb.begin_mail(
    sender => p_sender,
    recipients => p_recipients,
    subject => p_subject,
    mime_type => 'text/html; charset=UTF-8');--p_mime_type);

    demo_mail_heb.write_text(
    conn => conn,
    message => p_data);

    demo_mail_heb.end_mail( conn => conn );
    END;

    -----------------------------------
    -- Sent Html Email with Attachment
    -----------------------------------
    procedure send_html_mail_attach (p_sender in varchar2 default null,
    p_recipients in varchar2 default null,
    p_subject in varchar2 default null,
    p_data in varchar2 default '<b>áå÷ø èåá òåìí - áãé÷ä</b',
    p_mime_type in varchar2 default 'text/html; charset=windows-1255',
    p_file_name in varchar2 default 'but_choose_file.gif',
    p_file_mime_type in varchar2 default 'application/pdf',
    p_file_URL in varchar2 default 'http://10.172.246.160:7777/i/but_choose_file.gif')
    is
    conn utl_smtp.connection;
    req utl_http.req;
    resp utl_http.resp;
    data RAW(200);
    v_mime_type varchar2(32767):=demo_mail.MULTIPART_MIME_TYPE;
    begin
    conn := demo_mail_heb.begin_mail(
    sender => p_sender,
    recipients => p_recipients,
    subject => p_subject,
    mime_type => v_mime_type);

    demo_mail_heb.attach_text(
    conn => conn,
    data => p_data,
    mime_type => 'text/html');

    demo_mail_heb.begin_attachment(
    conn => conn,
    mime_type => p_file_mime_type,
    inline => TRUE,
    filename => p_file_name,
    transfer_enc => 'base64');

    -- In writing Base-64 encoded text following the MIME format below,
    -- the MIME format requires that a long piece of data must be splitted
    -- into multiple lines and each line of encoded data cannot exceed
    -- 80 characters, including the new-line characters. Also, when
    -- splitting the original data into pieces, the length of each chunk
    -- of data before encoding must be a multiple of 3, except for the
    -- last chunk. The constant demo_mail_heb.MAX_BASE64_LINE_WIDTH
    -- (76 / 4 * 3 = 57) is the maximum length (in bytes) of each chunk
    -- of data before encoding.

    req := utl_http.begin_request(p_file_URL);
    resp := utl_http.get_response(req);

    BEGIN
    LOOP
    utl_http.read_raw(resp, data, demo_mail_heb.MAX_BASE64_LINE_WIDTH);
    demo_mail_heb.write_raw(
    conn => conn,
    message => utl_encode.base64_encode(data));
    END LOOP;
    EXCEPTION
    WHEN utl_http.end_of_body THEN
    utl_http.end_response(resp);
    END;
    demo_mail_heb.end_attachment( conn => conn );


    demo_mail_heb.end_mail( conn => conn );

    end;


    -- Return the next email address in the list of email addresses, separated
    -- by either a "," or a ";". The format of mailbox may be in one of these:
    -- someone@some-domain
    -- "Someone at some domain" <someone@some-domain>
    -- Someone at some domain <someone@some-domain>
    FUNCTION get_address(addr_list IN OUT VARCHAR2) RETURN VARCHAR2 IS

    addr VARCHAR2(256);
    i pls_integer;

    FUNCTION lookup_unquoted_char(str IN VARCHAR2,
    chrs IN VARCHAR2) RETURN pls_integer AS
    c VARCHAR2(5);
    i pls_integer;
    len pls_integer;
    inside_quote BOOLEAN;
    BEGIN
    inside_quote := false;
    i := 1;
    len := length(str);
    WHILE (i <= len) LOOP

    c := substr(str, i, 1);

    IF (inside_quote) THEN
    IF (c = '"') THEN
    inside_quote := false;
    ELSIF (c = '\') THEN
    i := i + 1; -- Skip the quote character
    END IF;
    GOTO next_char;
    END IF;

    IF (c = '"') THEN
    inside_quote := true;
    GOTO next_char;
    END IF;

    IF (instr(chrs, c) >= 1) THEN
    RETURN i;
    END IF;

    <<next_char>>
    i := i + 1;

    END LOOP;

    RETURN 0;

    END;

    BEGIN

    addr_list := ltrim(addr_list);
    i := lookup_unquoted_char(addr_list, ',;');
    IF (i >= 1) THEN
    addr := substr(addr_list, 1, i - 1);
    addr_list := substr(addr_list, i + 1);
    ELSE
    addr := addr_list;
    addr_list := '';
    END IF;

    i := lookup_unquoted_char(addr, '<');
    IF (i >= 1) THEN
    addr := substr(addr, i + 1);
    i := instr(addr, '>');
    IF (i >= 1) THEN
    addr := substr(addr, 1, i - 1);
    END IF;
    END IF;

    RETURN addr;
    END;

    -- Write a MIME header
    PROCEDURE write_mime_header(conn IN OUT NOCOPY utl_smtp.connection,
    name IN VARCHAR2,
    value IN VARCHAR2) IS
    BEGIN
    -- utl_smtp.write_data(conn, name || ': ' || value || utl_tcp.CRLF);
    utl_smtp.write_raw_data(conn, UTL_RAW.CAST_TO_RAW(name || ': ' ||value || utl_tcp.CRLF));
    END;

    -- Mark a message-part boundary. Set <last> to TRUE for the last boundary.
    PROCEDURE write_boundary(conn IN OUT NOCOPY utl_smtp.connection,
    last IN BOOLEAN DEFAULT FALSE) AS
    BEGIN
    IF (last) THEN
    utl_smtp.write_data(conn, LAST_BOUNDARY);
    ELSE
    utl_smtp.write_data(conn, FIRST_BOUNDARY);
    END IF;
    END;

    ------------------------------------------------------------------------
    PROCEDURE mail(sender IN VARCHAR2,
    recipients IN VARCHAR2,
    subject IN VARCHAR2,
    message IN VARCHAR2) IS
    conn utl_smtp.connection;
    BEGIN
    conn := begin_mail(sender, recipients, subject);
    write_text(conn, message);
    end_mail(conn);
    END;

    ------------------------------------------------------------------------
    FUNCTION begin_mail(sender IN VARCHAR2,
    recipients IN VARCHAR2,
    subject IN VARCHAR2,
    mime_type IN VARCHAR2 DEFAULT 'text/plain',
    priority IN PLS_INTEGER DEFAULT NULL)
    RETURN utl_smtp.connection IS
    conn utl_smtp.connection;
    BEGIN
    conn := begin_session;
    begin_mail_in_session(conn, sender, recipients, subject, mime_type,
    priority);
    RETURN conn;
    END;

    ------------------------------------------------------------------------
    PROCEDURE write_text(conn IN OUT NOCOPY utl_smtp.connection,
    message IN VARCHAR2) IS
    BEGIN
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(CONVERT(message,'IW8ISO8859P8')));
    -- utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(message));

    END;

    ------------------------------------------------------------------------
    PROCEDURE write_mb_text(conn IN OUT NOCOPY utl_smtp.connection,
    message IN VARCHAR2) IS
    BEGIN
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(message));
    END;

    ------------------------------------------------------------------------
    PROCEDURE write_raw(conn IN OUT NOCOPY utl_smtp.connection,
    message IN RAW) IS
    BEGIN
    utl_smtp.write_raw_data(conn, message);
    END;

    ------------------------------------------------------------------------
    PROCEDURE attach_text(conn IN OUT NOCOPY utl_smtp.connection,
    data IN VARCHAR2,
    mime_type IN VARCHAR2 DEFAULT 'text/plain',
    inline IN BOOLEAN DEFAULT TRUE,
    filename IN VARCHAR2 DEFAULT NULL,
    last IN BOOLEAN DEFAULT FALSE) IS
    BEGIN
    begin_attachment(conn, mime_type, inline, filename);
    write_text(conn, data);
    end_attachment(conn, last);
    END;

    ------------------------------------------------------------------------
    PROCEDURE attach_base64(conn IN OUT NOCOPY utl_smtp.connection,
    data IN RAW,
    mime_type IN VARCHAR2 DEFAULT 'application/octet',
    inline IN BOOLEAN DEFAULT TRUE,
    filename IN VARCHAR2 DEFAULT NULL,
    last IN BOOLEAN DEFAULT FALSE) IS
    i PLS_INTEGER;
    len PLS_INTEGER;
    BEGIN

    begin_attachment(conn, mime_type, inline, filename, 'base64');

    -- Split the Base64-encoded attachment into multiple lines
    i := 1;
    len := utl_raw.length(data);
    WHILE (i < len) LOOP
    IF (i + MAX_BASE64_LINE_WIDTH < len) THEN
    utl_smtp.write_raw_data(conn,
    utl_encode.base64_encode(utl_raw.substr(data, i,
    MAX_BASE64_LINE_WIDTH)));
    ELSE
    utl_smtp.write_raw_data(conn,
    utl_encode.base64_encode(utl_raw.substr(data, i)));
    END IF;
    utl_smtp.write_data(conn, utl_tcp.CRLF);
    i := i + MAX_BASE64_LINE_WIDTH;
    END LOOP;

    end_attachment(conn, last);

    END;

    ------------------------------------------------------------------------
    PROCEDURE begin_attachment(conn IN OUT NOCOPY utl_smtp.connection,
    mime_type IN VARCHAR2 DEFAULT 'text/plain',
    inline IN BOOLEAN DEFAULT TRUE,
    filename IN VARCHAR2 DEFAULT NULL,
    transfer_enc IN VARCHAR2 DEFAULT NULL) IS
    BEGIN
    write_boundary(conn);
    write_mime_header(conn, 'Content-Type', mime_type);

    IF (filename IS NOT NULL) THEN
    IF (inline) THEN
    write_mime_header(conn, 'Content-Disposition',
    'inline; filename="'||filename||'"');
    ELSE
    write_mime_header(conn, 'Content-Disposition',
    'attachment; filename="'||filename||'"');
    END IF;
    END IF;

    IF (transfer_enc IS NOT NULL) THEN
    write_mime_header(conn, 'Content-Transfer-Encoding', transfer_enc);
    END IF;

    utl_smtp.write_data(conn, utl_tcp.CRLF);
    END;

    ------------------------------------------------------------------------
    PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,
    last IN BOOLEAN DEFAULT FALSE) IS
    BEGIN
    utl_smtp.write_data(conn, utl_tcp.CRLF);
    IF (last) THEN
    write_boundary(conn, last);
    END IF;
    END;

    ------------------------------------------------------------------------
    PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection) IS
    BEGIN
    end_mail_in_session(conn);
    end_session(conn);
    END;

    ------------------------------------------------------------------------
    FUNCTION begin_session RETURN utl_smtp.connection IS
    conn utl_smtp.connection;
    BEGIN
    -- open SMTP connection
    conn := utl_smtp.open_connection(smtp_host, smtp_port);
    utl_smtp.helo(conn, smtp_domain);
    RETURN conn;
    END;

    ------------------------------------------------------------------------

    ---------------------
    -- Handling the Email Subject Line
    ---------------------
    function mimeheader_encode(
    p_str varchar2
    , p_charset varchar2 := 'UTF-8') return varchar2 is
    l_str varchar2(2000);
    begin
    l_str:=utl_raw.cast_to_varchar2(utl_encode.quoted_printable_encode(utl_raw.cast_to_raw(p_str)));
    l_str:=replace(l_str,'='||chr(13)||chr(10),''); --unfold the data
    l_str:=replace(l_str,'?','=3f'); --quote question marks
    l_str:=replace(l_str,' ','=20'); --quote spaces
    l_str:='=?'||p_charset||'?Q?'||l_str||'?='; -- add prefix and suffix
    return l_str;
    end;

    PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,
    sender IN VARCHAR2,
    recipients IN VARCHAR2,
    subject IN VARCHAR2,
    mime_type IN VARCHAR2 DEFAULT 'text/plain',
    -- mime_type IN VARCHAR2 DEFAULT 'text/plain; charset=windows-1255',
    priority IN PLS_INTEGER DEFAULT NULL) IS
    my_recipients VARCHAR2(32767) := recipients;
    my_sender VARCHAR2(32767) := sender;

    BEGIN
    -- Specify sender's address (our server allows bogus address
    -- as long as it is a full email address (xxx@yyy.com).
    utl_smtp.mail(conn, get_address(my_sender));

    -- Specify recipient(s) of the email.
    WHILE (my_recipients IS NOT NULL) LOOP
    utl_smtp.rcpt(conn, get_address(my_recipients));
    END LOOP;

    -- Start body of email
    utl_smtp.open_data(conn);

    -- Set "From" MIME header
    write_mime_header(conn, 'From', sender);

    -- Set "To" MIME header
    write_mime_header(conn, 'To', recipients);


    -- Set "Content-Type" MIME header
    write_mime_header(conn, 'Content-Type', mime_type);
    -- write_mime_header(conn, 'Content-Type', 'text/html; charset=UTF-8');

    -- Set "Subject" MIME header
    -- write_mime_header(conn, 'Subject', subject);
    -- write_mime_header(conn, 'Subject', CONVERT(subject,'IW8ISO8859P8'));
    write_mime_header(conn, 'Subject',mimeheader_encode(p_str => subject,p_charset => 'UTF-8'));
    -- write_mime_header(conn, 'Subject',CONVERT(subject,'IW8MSWIN1255'));


    -- Set "X-Mailer" MIME header
    write_mime_header(conn, 'X-Mailer', MAILER_ID);

    -- Set priority:
    -- High Normal Low
    -- 1 2 3 4 5
    IF (priority IS NOT NULL) THEN
    write_mime_header(conn, 'X-Priority', priority);
    END IF;

    -- Send an empty line to denotes end of MIME headers and
    -- beginning of message body.
    utl_smtp.write_data(conn, utl_tcp.CRLF);

    IF (mime_type LIKE 'multipart/mixed%') THEN
    write_text(conn, 'This is a multi-part message in MIME format.' ||
    utl_tcp.crlf);

    END IF;
    END;

    ------------------------------------------------------------------------
    PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection) IS
    BEGIN
    utl_smtp.close_data(conn);
    END;

    ------------------------------------------------------------------------
    PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection) IS
    BEGIN
    utl_smtp.quit(conn);
    END;

    END;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points