Twitter Linkedin RSS
Close
Oracle

Sending Email with Oracle, Part 9: Alternative content

Sean D. Stuber

Sending Email with Oracle

All of the examples in prior articles assume the client will be able to render the content as we intended.

Some email clients however, will not; either because the software does not support certain features, or the recipient has disabled them.

It’s not feasible in the general case to construct and send different email bodies for each recipient’s email client. Instead, you can embed multiple formats in a single message using the multipart/alternative MIME type and then the client can render whichever of the alternate contents it is configured for. As with the mixed type, you will specify a boundary string to delimit different blocks. The difference between the types though is the mixed type tells the client to render all of the blocks within it. The alternative type on the other hand, tells the client to only render one branch of the available child trees. This is not just a matter of personal preference for the clients, it can also be helpful for software parsing. For example, text/plain content will be much easier to process for speech-to-text tools compared to the same content read from text/html.

This is the same mechanism APEX_MAIL package uses the p_body and p_body_html parameters.

The basic structure of such an email would look something like this…

MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="alternative_boundary"

--alternative_boundary
Content-Type: text/plain; 

sample text

--alternative_boundary
Content-Type: text/html;

<html>    
    <body>
        <h1>sample text</h1>
    </body>
</html>

--alternative_boundary--

When ordering the blocks, clients should render the last block they are capable of processing. Thus, if a client can render both plain text and HTML (most can) it is usual to put the plain text first and the HTML last so the richer formatting is preferred but the plain text is still available if needed.

Also note, it is not technically necessary to have the alternative blocks mirror each other in content, but it is recommended they do. Spam filters may flag the email if there is too much difference in the content.

Nesting MIME blocks

In the prior articles all of the MIME content was at the same scope level. That is, the plain and the html were at the same level within the email body. Neither was a child of the other. Even in the related content with a root block and referenced blocks they were still all at the same level, delimited by the same boundaries.

That is still possible with multipart/alternative blocks. The sample outline above illustrates such an email, with the text/plain and the text/html both being children of the main email body itself.

But, when using alternative content, while the text/plain may be at the top level, you may want the richer and fully formatted content to be multipart itself. Thereby requiring multipart/alternative blocks as a parent with multipart/related as a child, with that child having html and image content blocks. That outline might looks something like this:

MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="alternative_boundary"

--alternative_boundary
Content-Type: text/plain; 

sample text

--alternative_boundary
Content-Type: multipart/related;  boundary="related_boundary"

--related_boundary
Content-Type: text/html;

<html>    
    <body>
        <h1>sample text</h1>
        <img src="cid:myimage">
    </body>
</html>
--related_boundary
Content-Type: image/jpeg
Content-Transfer-Encoding: base64
Content-ID: <0123456789>
Content-Location: smiley.jpg

/9j/4AAQSkZJRgAB
...
AQEAYAAP//Z
--related_boundary--
--alternative_boundary--

Sending such an email with UTL_SMTP, following similar patterns as in earlier articles might look something like this:

DECLARE
    v_connection   UTL_SMTP.connection;
    v_clob         CLOB;
    v_len          INTEGER;
    v_index        INTEGER;
    v_raw_image    RAW(2000)
        := HEXTORAW(
                  'FFD8FFE000104A46494600010101006000600000FFDB004300020101020101020'
               || '2020202020202030503030303030604040305070607070706070708090B090808'
               || '0A0807070A0D0A0A0B0C0C0C0C07090E0F0D0C0E0B0C0C0CFFDB0043010202020'
               || '30303060303060C0807080C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C'
               || '0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0CFFC000110'
               || '8000A000A03012200021101031101FFC4001F0000010501010101010100000000'
               || '000000000102030405060708090A0BFFC400B5100002010303020403050504040'
               || '000017D01020300041105122131410613516107227114328191A1082342B1C115'
               || '52D1F02433627282090A161718191A25262728292A3435363738393A434445464'
               || '748494A535455565758595A636465666768696A737475767778797A8384858687'
               || '88898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C'
               || '4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7'
               || 'F8F9FAFFC4001F010003010101010101010101000000000000010203040506070'
               || '8090A0BFFC400B511000201020404030407050404000102770001020311040521'
               || '31061241510761711322328108144291A1B1C109233352F0156272D10A162434E'
               || '125F11718191A262728292A35363738393A434445464748494A53545556575859'
               || '5A636465666768696A737475767778797A82838485868788898A9293949596979'
               || '8999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4'
               || 'D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C030100021'
               || '10311003F00FD8FF8A1ADDE7FC241E3192DBE286BBA0DB2B6990ADADB5858CB36'
               || '813DACE93CB0DB43242D2DCBEA514C90E0ACB8C2F95B5D80AF6DAA17DE16D2F53'
               || 'D56DEFEE74DB0B8BEB4C791712DBA3CB0E338DAC4657193D0F7357E803FFFD9');
BEGIN
    v_clob := encode_blob_as_base64_clob(v_raw_image);

    v_connection :=
        UTL_SMTP.open_connection(
            HOST                            =&gt; 'smtp.gmail.com',
            port                            =&gt; 587,
            wallet_path                     =&gt; 'file:/u01/app/oracle/wallets/gmail',
            -- password not needed because wallet is auto login
            secure_connection_before_smtp   =&gt; FALSE);

    UTL_SMTP.ehlo(v_connection, 'smtp.gmail.com');
    UTL_SMTP.starttls(v_connection);
    UTL_SMTP.auth(v_connection, 'sean.stuber@gmail.com', :password, 'PLAIN');

    UTL_SMTP.mail(v_connection, 'sean.stuber@gmail.com'); --from
    UTL_SMTP.rcpt(v_connection, 'sean.stuber@gmail.com'); --- to
    UTL_SMTP.open_data(v_connection);

    UTL_SMTP.write_data(v_connection,
                        'From: sean.stuber@gmail.com' || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'To: sean.stuber@gmail.com' || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'Subject: test alternate content' || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'MIME-Version: 1.0' || UTL_TCP.crlf);

    UTL_SMTP.write_data(
        v_connection,
           'Content-Type: multipart/alternative; boundary=&quot;alternative_boundary&quot;'
        || UTL_TCP.crlf
        || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_connection, '--alternative_boundary' || UTL_TCP.crlf);
    UTL_SMTP.write_data(
        v_connection,
        'Content-Type: text/plain;' || UTL_TCP.crlf || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_connection, '   Here is a smile :)' || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_connection, '--alternative_boundary' || UTL_TCP.crlf);
    UTL_SMTP.write_data(
        v_connection,
           'Content-Type: multipart/related; boundary=&quot;related_boundary&quot;'
        || UTL_TCP.crlf
        || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_connection, '--related_boundary' || UTL_TCP.crlf);
    UTL_SMTP.write_data(
        v_connection,
        'Content-Type: text/html;' || UTL_TCP.crlf || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, '
&lt;html&gt;
    &lt;body&gt;
        Here is a smile &lt;img src=&quot;cid:0123456789&quot;&gt;
    &lt;/body&gt;
&lt;/html&gt;' || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_connection, '--related_boundary' || UTL_TCP.crlf);

    UTL_SMTP.write_data(
        v_connection,
           'Content-Type: image/jpeg'
        || UTL_TCP.crlf
        || 'Content-Transfer-Encoding: base64'
        || UTL_TCP.crlf
        || 'Content-ID: &lt;0123456789&gt;'
        || UTL_TCP.crlf
        || 'Content-Location: smiley.jpg'
        || UTL_TCP.crlf
        || UTL_TCP.crlf);

    v_len := DBMS_LOB.getlength(v_clob);
    v_index := 1;

    WHILE v_index &lt;= v_len
    LOOP
        UTL_SMTP.write_data(v_connection,
                            DBMS_LOB.SUBSTR(v_clob, 32000, v_index));
        v_index := v_index + 32000;
    END LOOP;

    UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);

    UTL_SMTP.write_data(v_connection, '--related_boundary__' || UTL_TCP.crlf);
    UTL_SMTP.write_data(
        v_connection,
        '--alternative_boundary--' || UTL_TCP.crlf || UTL_TCP.crlf);

    UTL_SMTP.close_data(v_connection);
    UTL_SMTP.quit(v_connection);
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
END;

As I mentioned in the MIME article I usually keep the construction of MIME structure itself separate from the content within the MIME blocks. However, if you wanted to construct the entire body first, including the MIME boundaries and block headers, the same email above might look something like this:

DECLARE
    v_connection   UTL_SMTP.connection;
    v_clob         CLOB;
    v_len          INTEGER;
    v_index        INTEGER;
    v_raw_image    RAW(2000)
        := HEXTORAW(
                  'FFD8FFE000104A46494600010101006000600000FFDB004300020101020101020'
               || '2020202020202030503030303030604040305070607070706070708090B090808'
               || '0A0807070A0D0A0A0B0C0C0C0C07090E0F0D0C0E0B0C0C0CFFDB0043010202020'
               || '30303060303060C0807080C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C'
               || '0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0CFFC000110'
               || '8000A000A03012200021101031101FFC4001F0000010501010101010100000000'
               || '000000000102030405060708090A0BFFC400B5100002010303020403050504040'
               || '000017D01020300041105122131410613516107227114328191A1082342B1C115'
               || '52D1F02433627282090A161718191A25262728292A3435363738393A434445464'
               || '748494A535455565758595A636465666768696A737475767778797A8384858687'
               || '88898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C'
               || '4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7'
               || 'F8F9FAFFC4001F010003010101010101010101000000000000010203040506070'
               || '8090A0BFFC400B511000201020404030407050404000102770001020311040521'
               || '31061241510761711322328108144291A1B1C109233352F0156272D10A162434E'
               || '125F11718191A262728292A35363738393A434445464748494A53545556575859'
               || '5A636465666768696A737475767778797A82838485868788898A9293949596979'
               || '8999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4'
               || 'D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C030100021'
               || '10311003F00FD8FF8A1ADDE7FC241E3192DBE286BBA0DB2B6990ADADB5858CB36'
               || '813DACE93CB0DB43242D2DCBEA514C90E0ACB8C2F95B5D80AF6DAA17DE16D2F53'
               || 'D56DEFEE74DB0B8BEB4C791712DBA3CB0E338DAC4657193D0F7357E803FFFD9');
BEGIN
    v_clob := '--alternative_boundary' || UTL_TCP.crlf;
    v_clob :=
        v_clob || 'Content-Type: text/plain;' || UTL_TCP.crlf || UTL_TCP.crlf;
    v_clob := v_clob || '   Here is a smile :)' || UTL_TCP.crlf;

    v_clob := v_clob || '--alternative_boundary' || UTL_TCP.crlf;
    v_clob :=
           v_clob
        || 'Content-Type: multipart/related; boundary="related_boundary"'
        || UTL_TCP.crlf
        || UTL_TCP.crlf;
    v_clob := v_clob || '--related_boundary' || UTL_TCP.crlf;
    v_clob :=
        v_clob || 'Content-Type: text/html;' || UTL_TCP.crlf || UTL_TCP.crlf;
    v_clob := v_clob || '

    
        Here is a smile 
    
' || UTL_TCP.crlf;

    v_clob := v_clob || '--related_boundary' || UTL_TCP.crlf;
    v_clob :=
           v_clob
        || 'Content-Type: image/jpeg'
        || UTL_TCP.crlf
        || 'Content-Transfer-Encoding: base64'
        || UTL_TCP.crlf
        || 'Content-ID: <0123456789>'
        || UTL_TCP.crlf
        || 'Content-Location: smiley.jpg'
        || UTL_TCP.crlf
        || UTL_TCP.crlf;

    DBMS_LOB.append(v_clob, encode_blob_as_base64_clob(v_raw_image));

    v_clob := v_clob || UTL_TCP.crlf;

    v_clob := v_clob || '--related_boundary__' || UTL_TCP.crlf;
    v_clob :=
        v_clob || '--alternative_boundary--' || UTL_TCP.crlf || UTL_TCP.crlf;

    v_connection :=
        UTL_SMTP.open_connection(
            HOST                            => 'smtp.gmail.com',
            port                            => 587,
            wallet_path                     => 'file:/u01/app/oracle/wallets/gmail',
            -- password not needed because wallet is auto login
            secure_connection_before_smtp   => FALSE);

    UTL_SMTP.ehlo(v_connection, 'smtp.gmail.com');
    UTL_SMTP.starttls(v_connection);
    UTL_SMTP.auth(v_connection, 'sean.stuber@gmail.com', :password, 'PLAIN');

    UTL_SMTP.mail(v_connection, 'sean.stuber@gmail.com'); --from
    UTL_SMTP.rcpt(v_connection, 'sean.stuber@gmail.com'); --- to
    UTL_SMTP.open_data(v_connection);

    UTL_SMTP.write_data(v_connection,
                        'From: sean.stuber@gmail.com' || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'To: sean.stuber@gmail.com' || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection,
                        'Subject: test alternate content' || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'MIME-Version: 1.0' || UTL_TCP.crlf);

    UTL_SMTP.write_data(
        v_connection,
           'Content-Type: multipart/alternative; boundary="alternative_boundary"'
        || UTL_TCP.crlf
        || UTL_TCP.crlf);

    v_len := DBMS_LOB.getlength(v_clob);
    v_index := 1;

    WHILE v_index <= v_len
    LOOP
        UTL_SMTP.write_data(v_connection,
                            DBMS_LOB.SUBSTR(v_clob, 32000, v_index));
        v_index := v_index + 32000;
    END LOOP;

    UTL_SMTP.close_data(v_connection);
    UTL_SMTP.quit(v_connection);
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
END;

Using this construction you can implement any number of alternate bodies for the client to render. In practice though it's often just two alternatives as shown above. One in plain text and another in HTML with formatting. In the last article of this series we'll pull of the MIME content types together to make a complete email with alternate content blocks of mixed types and attachments.