Sending Email with Oracle, Part 10: Pulling it all together

Sending Email with Oracle

In this series, parts 6 through 9 covered various MIME block and multipart types. Now we’ll put them all together. Taking all these steps may seem contrived but a structure such as this will likely be used by most modern email clients you may already be using.

In the code samples below the email constructed consists of a short message, a smiley face, and a small 2-column table of sample data. The data is also sent along in the form of a CSV file attachment. The message, face, and table will be presented in two alternative formats, HTML and plain text.

The HTML form of the email should render something like this:

Here’s your data, a CSV version of the content is in the attachment.

column1	column2
data 1a	data 1b
data 2a	data 2b
data 3a	data 3b
data 4a	data 4b

The plain text form of the email should look something like this:

Here's your data, a CSV version of the content is in the attachment.

:)

column1   column2
data 1a   data 1b
data 2a   data 2b
data 3a   data 3b
data 4a   data 4b

Despite the simplicity of the final, rendered output, there is a lot going on in the nested MIME definitions. It can be difficult to read through them with common randomly generated boundaries. To aid in reading, I have constructed boundaries that help describe their scope. Before jumping into the full email body content lets look at an outline of the MIME nesting.

Since there is both a message and an attachment the outermost MIME time, that one defined in the email header is declared as multipart/mixed. The child blocks of this are the alternative block and the file attachment. If there was more than one file attached, they would all be at this same outer scope.

Within the alternative type there is text/plain block holding the plain text form of the message. The other alternative is yet another multipart layer of related content.

The root of the related content is an HTML block, with a related block for an embedded image content. The block scope hierarchy can be read as follows:

Content-Type: multipart/mixed; boundary="----OuterMixedBoundary"
------OuterMixedBoundary
    Content-Type: multipart/alternative; boundary="----SubBlockAlternativeBoundary"
    ------SubBlockAlternativeBoundary
        Content-Type: text/plain;
        ...
    ------SubBlockAlternativeBoundary
        Content-Type: multipart/related; boundary="----InnerRelatedBoundary"
        ------InnerRelatedBoundary
            Content-Type: text/html; 
            ...
        ------InnerRelatedBoundary
            Content-Type: image/jpeg; name="smiley.jpg"
            Content-Disposition: inline; filename="smiley.jpg"
            Content-Id: 
            Content-Transfer-Encoding: base64
            ...
        ------InnerRelatedBoundary--
    ------SubBlockAlternativeBoundary--
------OuterMixedBoundary
    Content-Type: text/plain; name="data.csv"
    Content-Disposition: attachment; filename="data.csv"
    ...
------OuterMixedBoundary--

For ease of illustration and reading, the code sample will send the entire MIME structure as a single, preconstructed varchar2 value. This mirrors the suggestion from earlier article of building all of the content before sending.

DECLARE
    v_connection   UTL_SMTP.connection;
BEGIN
    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 all together' || UTL_TCP.crlf);
    UTL_SMTP.write_data(
        v_connection,
           'Content-Type: multipart/mixed; boundary="----OuterMixedBoundary"'
        || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'MIME-Version: 1.0' || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);

    UTL_SMTP.write_data(
        v_connection,
           q'[
------OuterMixedBoundary
Content-Type: multipart/alternative; boundary="----SubBlockAlternativeBoundary"

------SubBlockAlternativeBoundary
Content-Type: text/plain;

Here's your data, a CSV version of the content is in the attachment.

:)

column1   column2
data 1a   data 1b
data 2a   data 2b
data 3a   data 3b
data 4a   data 4b


------SubBlockAlternativeBoundary
Content-Type: multipart/related; boundary="----InnerRelatedBoundary"

------InnerRelatedBoundary
Content-Type: text/html; 

<html>
<head>
<style>
table, th, td {
  border: 1px solid black;
  border-collapse: collapse;   https://seanstuber.com/wp-content/uploads/2022/04/smiley-1.jpg
}
</style>
</head>
<body>
<h3>Here's your data, a CSV version of the content is in the attachment.</h3>
<img src="cid:smileyface">
<br>
<br>
<table style="width:25%")>
<tr style="background-color: #999966;"><th>column1</th><th>column2</th></tr>
<tr style="background-color: #f5f5ef;"><td>data 1a</td><td>data 1b</td></tr>
<tr style="background-color: #d8d8c0;"><td>data 2a</td><td>data 2b</td></tr>
<tr style="background-color: #f5f5ef;"><td>data 3a</td><td>data 3b</td></tr>
<tr style="background-color: #d8d8c0;"><td>data 4a</td><td>data 4b</td></tr>
</table>
</body>
</html>

------InnerRelatedBoundary
Content-Type: image/jpeg; name="smiley.jpg"
Content-Disposition: inline; filename="smiley.jpg"
Content-Id: <smileyface>
Content-Transfer-Encoding: base64

/9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAIBAQIBAQICAgICAgICAwUDAwMDAwYEBAMFBwYH
BwcGBwcICQsJCAgKCAcHCg0KCgsMDAwMBwkODw0MDgsMDAz/2wBDAQICAgMDAwYDAwYMCAcI
DAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAz/wAAR
CAAKAAoDASIAAhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAA
AgEDAwIEAwUFBAQAAAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkK
FhcYGRolJicoKSo0NTY3ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWG
h4iJipKTlJWWl5iZmqKjpKWmp6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl
5ufo6erx8vP09fb3+Pn6/8QAHwEAAwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREA
AgECBAQDBAcFBAQAAQJ3AAECAxEEBSExBhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYk
NOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElKU1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOE
hYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk
5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD9j/ihrd5/wkHjGS2+KGu6DbK2mQra21hY
yzaBPazpPLDbQyQtLcvqUUyQ4Ky4wvlbXYCvbaoX3hbS9T1W3v7nTbC4vrTHkXEtujyw4zja
xGVxk9D3NX6AP//Z

------InnerRelatedBoundary--

------SubBlockAlternativeBoundary--
------OuterMixedBoundary
Content-Type: text/plain; name="data.csv"
Content-Disposition: attachment; filename="data.csv"

"column1","column2"
"data 1a","data 1b"
"data 2a","data 2b"
"data 3a","data 3b"
"data 4a","data 4b"

------OuterMixedBoundary--]'
        || 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;

For relatively short content I might try to build the content serially to create the entire body. More likely though, especially as the content gets large and with more blocks I would prefer to build it through a series of nested procedure or function calls. Each parent procedure defining a boundary and block header and then calling child routines to fill in the content, delimiting them as I went.

An outline of code to build the MIME content above might look something like this:

DECLARE
    v_connection   UTL_SMTP.connection;
    v_body         VARCHAR2(32767);

    PROCEDURE start_email(p_connection OUT UTL_SMTP.connection)
    IS
    BEGIN
        p_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(p_connection, 'smtp.gmail.com');
        UTL_SMTP.starttls(p_connection);
        UTL_SMTP.auth(p_connection,
                      'sean.stuber@gmail.com',
                      :password,
                      'PLAIN');

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

        UTL_SMTP.write_data(p_connection,
                            'From: sean.stuber@gmail.com' || UTL_TCP.crlf);
        UTL_SMTP.write_data(p_connection,
                            'To: sean.stuber@gmail.com' || UTL_TCP.crlf);
        UTL_SMTP.write_data(p_connection,
                            'Subject: test all together 2' || UTL_TCP.crlf);
        UTL_SMTP.write_data(p_connection, 'MIME-Version: 1.0' || UTL_TCP.crlf);
    END;

    FUNCTION plain_text_content
        RETURN VARCHAR2
    IS
    BEGIN
        RETURN q'[Content-Type: text/plain;

Here's your data, a CSV version of the content is in the attachment.

:)

column1   column2
data 1a   data 1b
data 2a   data 2b
data 3a   data 3b
data 4a   data 4b
]';
    END;

    FUNCTION html_content
        RETURN VARCHAR2
    IS
    BEGIN
        RETURN q'[Content-Type: text/html; 

<html>
<head>
<style>
table, th, td {
  border: 1px solid black;
  border-collapse: collapse;   https://seanstuber.com/wp-content/uploads/2022/04/smiley-1.jpg
}
</style>
</head>
<body>
<h3>Here's your data, a CSV version of the content is in the attachment.</h3>
<img src="cid:smileyface">
<br>
<br>
<table style="width:25%")>
<tr style="background-color: #999966;"><th>column1</th><th>column2</th></tr>
<tr style="background-color: #f5f5ef;"><td>data 1a</td><td>data 1b</td></tr>
<tr style="background-color: #d8d8c0;"><td>data 2a</td><td>data 2b</td></tr>
<tr style="background-color: #f5f5ef;"><td>data 3a</td><td>data 3b</td></tr>
<tr style="background-color: #d8d8c0;"><td>data 4a</td><td>data 4b</td></tr>
</table>
</body>
</html>
]';
    END;

    FUNCTION embedded_image
        RETURN VARCHAR2
    IS
    BEGIN
        RETURN q'[Content-Type: image/jpeg; name="smiley.jpg"
Content-Disposition: inline; filename="smiley.jpg"
Content-Id: <smileyface>
Content-Transfer-Encoding: base64

/9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAIBAQIBAQICAgICAgICAwUDAwMDAwYEBAMFBwYH
BwcGBwcICQsJCAgKCAcHCg0KCgsMDAwMBwkODw0MDgsMDAz/2wBDAQICAgMDAwYDAwYMCAcI
DAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAz/wAAR
CAAKAAoDASIAAhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAA
AgEDAwIEAwUFBAQAAAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkK
FhcYGRolJicoKSo0NTY3ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWG
h4iJipKTlJWWl5iZmqKjpKWmp6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl
5ufo6erx8vP09fb3+Pn6/8QAHwEAAwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREA
AgECBAQDBAcFBAQAAQJ3AAECAxEEBSExBhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYk
NOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElKU1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOE
hYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk
5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD9j/ihrd5/wkHjGS2+KGu6DbK2mQra21hY
yzaBPazpPLDbQyQtLcvqUUyQ4Ky4wvlbXYCvbaoX3hbS9T1W3v7nTbC4vrTHkXEtujyw4zja
xGVxk9D3NX6AP//Z
]';
    END;

    FUNCTION file_attachment
        RETURN VARCHAR2
    IS
    BEGIN
        RETURN q'[Content-Type: text/plain; name="data.csv"
Content-Disposition: attachment; filename="data.csv"

"column1","column2"
"data 1a","data 1b"
"data 2a","data 2b"
"data 3a","data 3b"
"data 4a","data 4b"
]';
    END;

    FUNCTION build_related_content
        RETURN VARCHAR2
    IS
        c_boundary   VARCHAR2(35) := '----InnerRelatedBoundary';
        v_output     VARCHAR2(32767);
    BEGIN
        v_output :=
            'Content-Type: multipart/related; boundary="' || c_boundary || '"';
        v_output := v_output || UTL_TCP.crlf;
        v_output := v_output || UTL_TCP.crlf;
        v_output := v_output || '--' || c_boundary || UTL_TCP.crlf;
        v_output := v_output || html_content;
        v_output := v_output || '--' || c_boundary || UTL_TCP.crlf;
        v_output := v_output || embedded_image;
        v_output := v_output || '--' || c_boundary || '--' || UTL_TCP.crlf;
        RETURN v_output;
    END;

    FUNCTION build_alternative_content
        RETURN VARCHAR2
    IS
        c_boundary   VARCHAR2(35) := '----SubBlockAlternativeBoundary';
        v_output     VARCHAR2(32767);
    BEGIN
        v_output :=
               'Content-Type: multipart/alternative; boundary="'
            || c_boundary
            || '"';
        v_output := v_output || UTL_TCP.crlf;
        v_output := v_output || UTL_TCP.crlf;
        v_output := v_output || '--' || c_boundary || UTL_TCP.crlf;
        v_output := v_output || plain_text_content;
        v_output := v_output || '--' || c_boundary || UTL_TCP.crlf;
        v_output := v_output || build_related_content;
        v_output := v_output || '--' || c_boundary || '--' || UTL_TCP.crlf;
        RETURN v_output;
    END;

    FUNCTION build_mixed_content
        RETURN VARCHAR2
    IS
        c_boundary   VARCHAR2(35) := '----OuterMixedBoundary';
        v_output     VARCHAR2(32767);
    BEGIN
        v_output :=
            'Content-Type: multipart/mixed; boundary="' || c_boundary || '"';
        v_output := v_output || UTL_TCP.crlf;
        v_output := v_output || UTL_TCP.crlf;
        v_output := v_output || '--' || c_boundary || UTL_TCP.crlf;
        v_output := v_output || build_alternative_content;
        v_output := v_output || '--' || c_boundary || UTL_TCP.crlf;
        v_output := v_output || file_attachment;
        v_output := v_output || '--' || c_boundary || '--' || UTL_TCP.crlf;
        RETURN v_output;
    END;
BEGIN
    v_body := build_mixed_content;

    start_email(v_connection);

    UTL_SMTP.write_data(v_connection, v_body || 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;

Before connecting to the SMTP server I build the email body. At the outer most layer is the mixed content. It then calls the alternative and attachment routines. The alternative function calls the plain text and related content. In the last layer of nesting, the related function calls the html and embedded image routines. Each routine is then responsible for its own content with the parent delimiting them as needed.

I hope you found this example helpful. I use it as a reference myself.
Thank you for reading. Questions and comments, as always, are welcome.