Send Mails from PL/SQL

Deutsche Version

Features
Restricions
Tutorial

General
As simple as possible
Short Mail
Long Mail
Attachments
Attachments (iCal)
Inline Images

Download
Disclaimer
Licence

This package sends plain text and HTML-E-mails from PL/SQL

mam_mail

Features

Plain text mails.
HTML-Mails
Send to CC and BCC
Addresses with aliases
Attachements as CLOB, BLOB or iCalendar

Restricions

This solution assumes that you use an internal smtp-host. If you want to use e.g. Gmail instead, then search for stunnel or "UTL_SMTP over SSL"

Sending email from Oracle XE using Gmail

Or use utl_smtp.auth

Tutorial

You might want to write a wrapper for this package that e.g. makes sure that in test/dev mails will be sent only to testers/developers instead of real external addresses. There's a blog post by Steven Feuerstein on this topic:

Managing Emails in Oracle Application Express

You can also use this wrapper to e.g. give HTML-mails a standardized header/footer and a default font.

General

E-mail addresse as comma separated list are supported

'my.address@example.com,another.address@example.com'

Aliases are allowed

'My Address <my.address@example.com>,Another Address <another.adress@example.com>'

If you always use the same mail server it might be useful to insert it for g_mailserver directly in the package body.

As simple as possible

The most simple call sends a plain text mail without bells and whistles

BEGIN
    -- You can omit the first call (see above),
    -- if you hard code your default mail server in the package body.
    mam_email.setMailserver('my.mailserver.example.com');
    mam_email.sendSimple(
         'recipient@example.com'-- Recipient
        ,'sender@example.com'-- Sender
        ,'Testmail'-- Subject
        ,'Mailtext'-- Mail text
        );
END;

Simple test mail

Short Mail

If the mail text (in case of HTML-mail incl. tags) is not more than 1000 characters long.

BEGIN
    mam_email.setMailserver('my.mailserver.example.com');
    mam_email.send(
         'recipient@example.com'-- Recipient
        ,'cc@example.com'-- Recipient of copy
        ,'bcc@example.com'-- Recipient of blind copy
        ,'sender@example.com'-- Sender
        ,'Testmail'-- Subject
        ,'This is a test mail'-- Unformatted text
        ,'<h2>Test</h2><p>This is a test mail</p>'-- Text as HTML
        );
END;

Kurze Mail mit HTML

Long Mail

DECLARE
    textbody        mam_email.tbl_mailbody;
    htmlbody        mam_email.tbl_mailbody;
    lineNumber      PLS_INTEGER := 1;
BEGIN
    htmlbody(lineNumber).line  :=
        '<div style="font: 1em/100% Arial, sans-serif;">';
    lineNumber := lineNumber + 1;
    htmlbody(lineNumber).line  :=
        '<table style="font: 1em/100% Arial, sans-serif;" width="95%" '||
        'border="1" cellspacing="0" cellpadding="4"><tbody>'||
        '<tr style="background-color: #c8c8c8">'||
         '<th>Number</th>'||
         '<th>String</th>'||
        '</tr>';
    lineNumber := lineNumber + 1;
    htmlbody(lineNumber).line  :=
        '<tr>'||
        '<td>1</td>'||
        '<td>ABC</td>'||
        '</tr>';
    lineNumber := lineNumber + 1;
    htmlbody(lineNumber).line  := '</tbody></table>';
    lineNumber := lineNumber + 1;
    htmlbody(lineNumber).line  := '<p>A table and some text</p>';
    lineNumber := lineNumber + 1;
    htmlbody(lineNumber).line  := '</div>';

    lineNumber := 1;
    textbody(lineNumber).line  := 'Number|String';
    lineNumber := lineNumber + 1;
    textbody(lineNumber).line  := '------|------';
    lineNumber := lineNumber + 1;
    textbody(lineNumber).line  := '    1 | ABC';
    lineNumber := lineNumber + 1;
    textbody(lineNumber).line  := 'Alternative text if HTML is not displayed';

    mam_email.setMailserver('my.mailserver.example.com');
    mam_email.send(
         'recipient@example.com'-- Recipient
        ,'cc@example.com'-- Recipient of copy
        ,'bcc@example.com'-- Recipient of blind copy
        ,'sender@example.com'-- Sender
        ,'Testmail'-- Subject
        ,textbody-- Unformatted text
        ,htmlbody-- Text as HTML
        );
END;

Mail with HTML table

Attachments

DECLARE
    attachments     mam_email.tbl_attachments;
BEGIN
    attachments(1).fileCLOB := 'a,b,c,d';
    attachments(1).fileName := 'abc.csv';
    mam_email.setMailserver('my.mailserver.example.com');
    mam_email.send(
         'recipient@example.com'-- Recipient
        ,'cc@example.com'-- Recipient of copy
        ,'bcc@example.com'-- Recipient of blind copy
        ,'sender@example.com'-- Sender
        ,'Testmail'-- Subject
        ,'Mail with CLOB attachment'
        ,'<div style="font: 1em/100% Arial, sans-serif;">'||
         '<p>Mail with CLOB attachment</p>'||
         '</div>'
        ,attachments-- Attachments
        );
END;

Mail with attachment

Attachments (iCal)

You can use the function mam_email.createEvent to generate a calendar element. The extension .ical will be added automatically to the filename if you omit it. If you don't provide a filename a default is used

DECLARE
    attachments     mam_email.tbl_attachments;
BEGIN
    attachments(1).fileName := 'Invitation-test';
    attachments(1).fileICAL := mam_email.createEvent(
                         sender      => 'Matzberger Marcus '
                        ,startDate   => SYSDATE + 5/1440
                        ,endDate     => SYSDATE + 10/1440
                        ,subject     => 'Test Event'
                        ,description => 'Description for test event \n Another line'
                        ,vLocation   => 'Somewhere over the rainbow'
                        ,vAlarm      => '15'
                        );
    mam_email.setMailserver('my.mailserver.example.com');
    mam_email.send(
         recipient   => 'recipient@example.com'
        ,sender      => 'sender@example.com'
        ,subject     => 'Testmail'
        ,bodyText    => 'Mail with iCal attachment'
        ,bodyHTML    => '<div style="font: 1em/100% Arial, sans-serif;">'||
                        '<p>Mail with iCal attachment</p>'||
                        '</div>'
        ,attachments => attachments
        );
END;

Inline Images

If you want images in HTML-mails to be displayed correctly, especially in Outlook, there is only one working option

If in an attachments-Record the attribute contentID is filled (e.g. 345678912), then you can referenct it in your HTML with <img src="cid:345678912">

In this example the image it re-encoded from a base64-string into a BLOB because I didn't want to have dependencies for this code. In real life the image could/should be included from a BLOB column

DECLARE
    tmpClob         CLOB;
    t_attachments   mam_email.tbl_attachments;

FUNCTION base64_decode
    (
     base64Data         IN CLOB
    )
    RETURN BLOB
IS

    position            PLS_INTEGER := 1;
    isLength            PLS_INTEGER;
    workLength          PLS_INTEGER := 48;
    readRawBuffer       RAW(32767);
    daten               VARCHAR2(32767);
    decodedBlob         BLOB := EMPTY_BLOB();

BEGIN

    dbms_lob.createTemporary(decodedBlob,TRUE,dbms_lob.CALL);
    isLength  := dbms_lob.getLength(base64Data);

    WHILE position <= isLength LOOP

        dbms_lob.read(base64Data,workLength,position,daten);
        readRawBuffer := utl_encode.base64_decode(utl_raw.cast_to_raw(daten));
        dbms_lob.writeAppend(
             decodedBlob
            ,utl_raw.length(readRawBuffer)
            ,readRawBuffer
            );

        position    := position + workLength;

    END LOOP;

    RETURN decodedBlob;

END base64_decode;

BEGIN

    tmpClob :=           'R0lGODlhoQHRAOYAAPR1efWBhfzZ2viipfeWme4lLO87QfilqPm1t/729vV/g/NjaPvQ0f74+PNtcf7x8e4qMfR6fvm0tvFQVvaKjveanfJgZfeeof3q6u0hKPq9v/FMUvm4uvJZXvvNz/mtsPBARe84P/7y8vBITvaPk+0fJvvJy/zc3u4uNO8yOe0gJ/3k5fFKUPrFxvNobfvHyfWGivJeY/zU1vaQlPrAwvBESvmws/BCSPmytPisrvq+wO80Ou4sM/FSWO82PO8wN/WEiO4nLfRzd/BHTMjIyNXV1faOkfRxdvFVWviqrf3j5Pecn//9/f/+/v/8/P3i4//7/P/7+//6+v3n6PJXXf7s7fvP0fego/eYm/zX2O4kK/7u7/iprPRwdP3s7P7t7vFUWf709f3o6firrfaSlfNrcPioq/zb3PaTlvaNkP7z9P3p6v3m5/V9gfJbYPzU1fJfZPaUl/vT1P3l5vq7vfrCxPJWW/zf4P3g4fzc3fze3/7v8PzV1/R3e+0dJP///yH/C1hNUCBEYXRhWE1QPD94cGFja2V0IGJlZ2luPSLvu78iIGlkPSJXNU0wTXBDZWhpSHpyZVN6TlRjemtjOWQiPz4gPHg6eG1wbWV0YSB4bWxuczp4PSJhZG9iZTpuczptZXRhLyIgeDp4bXB0az0iQWRvYmUgWE1QIENvcmUgNS42LWMwNjcgNzkuMTU3NzQ3LCAyMDE1LzAzLzMwLTIzOjQwOjQyICAgICAgICAiPiA8cmRmOlJERiB4bWxuczpyZGY9Imh0dHA6Ly93d3cudzMub3JnLzE5OTkvMDIvMjItcmRmLXN5bnRheC1ucyMiPiA8cmRmOkRlc2NyaXB0aW9uIHJkZjphYm91dD0iIiB4bWxuczp4bXA9Imh0dHA6Ly9ucy5hZG9iZS5jb20veGFwLzEuMC8iIHhtbG5zOnhtcE1NPSJodHRwOi8vbnMuYWRvYmUuY29tL3hhcC';
    tmpClob := tmpClob||'8xLjAvbW0vIiB4bWxuczpzdFJlZj0iaHR0cDovL25zLmFkb2JlLmNvbS94YXAvMS4wL3NUeXBlL1Jlc291cmNlUmVmIyIgeG1wOkNyZWF0b3JUb29sPSJBZG9iZSBQaG90b3Nob3AgQ0MgMjAxNSAoTWFjaW50b3NoKSIgeG1wTU06SW5zdGFuY2VJRD0ieG1wLmlpZDo5MzFFRUI0RTUwMjAxMUU2Qjg3NEY3NDNBNjMyRTEzRSIgeG1wTU06RG9jdW1lbnRJRD0ieG1wLmRpZDo5MzFFRUI0RjUwMjAxMUU2Qjg3NEY3NDNBNjMyRTEzRSI+IDx4bXBNTTpEZXJpdmVkRnJvbSBzdFJlZjppbnN0YW5jZUlEPSJ4bXAuaWlkOjkzMUVFQjRDNTAyMDExRTZCODc0Rjc0M0E2MzJFMTNFIiBzdFJlZjpkb2N1bWVudElEPSJ4bXAuZGlkOjkzMUVFQjRENTAyMDExRTZCODc0Rjc0M0E2MzJFMTNFIi8+IDwvcmRmOkRlc2NyaXB0aW9uPiA8L3JkZjpSREY+IDwveDp4bXBtZXRhPiA8P3hwYWNrZXQgZW5kPSJyIj8+Af/+/fz7+vn49/b19PPy8fDv7u3s6+rp6Ofm5eTj4uHg397d3Nva2djX1tXU09LR0M/OzczLysnIx8bFxMPCwcC/vr28u7q5uLe2tbSzsrGwr66trKuqqainpqWko6KhoJ+enZybmpmYl5aVlJOSkZCPjo2Mi4qJiIeGhYSDgoGAf359fHt6eXh3dnV0c3JxcG9ubWxramloZ2ZlZGNiYWBfXl1cW1pZWFdWVVRTUlFQT05NTEtKSUhHRkVEQ0JBQD8+PTw7Ojk4NzY1NDMyMTAvLi0sKyopKCcmJSQjIiEgHx4dHBsaGRgXFhUUExIREA8ODQwLCgkIBwYFBAMCAQAAIfkEAAAAAAAsAAAAAKEB0QAAB/+ARIKDhIWGh4iJiouMjY6PkJGSk5SV';
    tmpClob := tmpClob||'lpeYmZqbnIx/n6ChoqOkpaanqKmqq6ytrq+wsbKztLW2t7i5pkVEur6/wMHCw8TFxsfIf7zJzM3Oz9DR0tOny9TX2Nna29zN1t3g4eLj5Nzf5ejp6uvssOft8PHy8+a99Pf4+frC7/v+/wADiuonsKDBg+sIIlzIsCE1hQ4jSpwYDCLFixgztrKosaNHjxw/ihzpMCTJkygBmkzJsqW8lS5jyiQHc6bNm9hq4tzJk5nOnkCDAvsptKjRWUSPKl2aKinTp1A/OY1K9ejUqliBXs3K9ebWrmBdfg1L9uTYsmhB2kvLVuvatnD/vb6NS1fs3Lp4zd7Ny1dt378izwIePE8w4cPsDCNeXE4x48fgHEOenE0y5cvSLGPe7G0v58/pNIMezc8z6dP1UKsWJ3q1a1mtX8veaHq27WOxb+smlXu3b6m1fwu/1Xv47eLGZyNP/no589XOn6OOLp009eqgr2PnrH075u7eKYMPD3k8ecbmzyNOr54w+/aA38PvK39+3vr26+LPH3c//7b+/ZdWgAKWRWCBYR2IYFcKLphVgw5WBWGEUU1I4VMWXrhUhhpaFVyHyn0IYnMijghdiSZOh2KK1q3IYnYuvshdjDJ+R2ON4t2IY3k67ohej/k8YAIOSRRp5JFIGmlD/wtrrIJAklBGaeQHHjRgixpXABABAkzcEoYVEkgpZhIcfBIGklYMA0UWdIwp5RhSfNKEBjAsgUFEHOIiBhlDlODHn4AGKuigfxoAQx6ohEDoooz+WUAXAsxygg+BUpHALA9cMIEKjXbqBxefKCGoAsBAIcECQXja6BGfMOGCBQRcsMEbJQEZzx5tZKDqrn6UUMYcpijKq6cqwOAELFKAMGgfsSSQBgTDLtpBE6GO+osNBkRLaAp7fIIAEApEMMQZVNQaUR0paLtrEDmUIqy6jC4AxSsULNrCK3woC2+gQTwBiqiBkprLHhbsKygCoATAgAILDCFFD+Y2dICfBntKAf8p71';
    tmpClob := tmpClob||'YsaBuu8KEroQZcyooGBWj85wWhAAyowLc8oa/GC4RCBgIKHDABFCxEvNAYjKbAAhJABy300EhskLGgaYySMQRENz30BtAO+gIrODcKBCstfDwoCiM47XQXXf5r7S0YZEtoASBM4DXRVIgRChsjmMEGAkeQoPNBVmgNqA8VAAtLFWbUQCjCoWSMBCxR2ICCoHawgoWgevfKgCor8LC1ESfUovKfLNMCRQ+DZtCFCXHS8kYHG2wQR9gM5Ynsy4DCYCUtTFyghaAofFF4oIfHckLUgPqbyh0lB3qBC4LWEEUqHQzaRbe2bO5H57M8LigLkQrl+itLCFrCB7mYUPz/yrsD2nssBAg6RipN2CHoBk6sAfyfM6DCwaBo5CI99bF8kWqgbhCZ9myFDifsQFBY8MX9AqWFLYDCcLN4gqAuhooDQC4LnxgA5M5wigkIiln6GxstjJC8MHgIITR4H+tyUQZBDeCBvJtFAkRYiinMzw8U/AMTPBioCawwFCcQlA8EeIv92eJoHlDK9lqhAEHRARhBDBQcYGi+WWRBUHY7xQIEBYLZfSILkUMZKSogKDGGMGC1EICgYrAhApZjA4HiwbGAAUdAoYCKfzofLAIgKAmcQgJSGwUMBBWEFZACeYAqQRV8YURamGGNRoikJCdJyUpS0gR+Ocj82BgMPgYK/3oQjIUGKPanDDiwFFtIV6AAQIoEmA1QbiDFCAIFgl80chaDNBnudNeRJbJCUBEQBhkDJbxQtqIJJwACpwJVhlMcQVApeEApUqg+pQWqA7akYSz6oEtB2eAjvlwFMIXRvUDh4ROhPMENIMDOdrrTnZErpR5MQc1A+dEULYzjncr3J07q4payAEA3ARUzcLqRHP/7U0GBAQRBeQGdMfzEFGYJrxeWIgxHswAqMGC5QLlAFIIDVA2yiUZaNHSgEJhCYA46jiEEKgXUAgYYAgUBPPpBj2qIgbryZ4om0pQNqbCgoMoEii0CSgVqYKQ2YSFU';
    tmpClob := tmpClob||'QE3gCFCNqlSnSlWoEtWgCP8RaKCm9ospkNIP2IRoFUPhhAgMawdXJYUHvuqHK6iiCaB7qTQ/QQZBgeqfS32FHAT1USWyVBwIEFRYfdEGQRHApnoERQXYGroeDICIo5DCDQQ1ARHEYG1Bg92fWPmJN3BxXmckH+1+EKgMcPCEB5FCRwEFPl14YJl/UoFKxZrHUnBgfIDqgRVksILlpSINkIuU9dR1r09MNlBGwGtJaeFJQIHBt0UJ5ypIGKgCFPcWJyBtoLrAz5uaQg6q5N0pUyGAyCXtD06oo7ZC9okkeK+1RczrK1YQOQDMcYALUcMBGXiAmNJCA4urriERe4oVhDRQBsjeKdLLxdL9obzwCsD/J5zAAu/NALSak+8rThooCzwUvwvRAaEmwAHouoIJLzCqoNza3cSOQgRuGBQENICKYQYKk6GgrrZKMLk/8OF2yfsAZGEBUBkeN44zcJtbGlJXQgUBDGWoqpSP4AAqrFZQrBKFMU0BBa0GqgQVMMUTcOsHEIYiChdYgprXzGY2zzRQyvuEDRalhQ24YMp4PoK/pHeDPPsZqgjLQ4AHBQI4dOHPVKVxRqTbinoNlFBluC+BVZG+QR3BwaBoQvMCtQMRzEIJZM7iHyY20IJKz2S5+4QctKvLHSR10X9FBxcS+ugSGMG/LWYFAoDcww+D4pFDrQUaggsKGgy6YhBo0h9O/62xe37iCRXWpaJhLRE8FOzRG0iTuyK6Cg+wGlAh4AMoMHDDhc4iWZRlnReEANt9mQEUCSBzxcz9iSgQ4IbqamYvY60OOfThyvAqgAtokChur8JlgwoCUVX8JwgouRYtGJQZP3EHIFAKXlTA9QW6CYF9juIBWKCoulCwyH1jhAl8sAEWLMlyScaBCx7AtCmWMMkkvGIPM6gkCTBQhUoSHBcHoCQWcA0KPSCgAi1vufBCwQAyJP3pk6xDKqqggSvkHOqTvK5GGO0jknC96ysFO4nE7pqvk33r/D47gN';
    tmpClob := tmpClob||'Ku9gGxve0GejvcEyT3uTOo7nZ/EN7zLqG9871Cfv87hgIv+P82Fv4yZj/8QRKv+IIwvvEBeTzk/yH5ye+j8pbPB+Yzf4/Nc74whP88TjwveniQvvSJCT3qZXL61YdG9a5vSetjTxPY0x4ls799OHKv+27wvvfb+D3wK2P74WfS+GgRPvKnofzlR6P5zn8G9KPfGerT3fpgmT72cVP87d/N+4AHP1a0L/7SlJ8q5D//L9Kvfl2wv/24eD/8bSH/+dOi/vaHTffz7w/88/8V/vd/tCGAIEaAPRGABtgU+5eAoMeAPIGADlgKEBiBozCBFBgKFniBwKGBM5GBGuiBFwiCFCiCEUiCDmiCDIiCCaiCBsiCBOiCAgiD/yeD/EeD+WeD9scWC52wgzzYgz74g0AYhEI4hERYhIIQCAA7';
    t_attachments(1).fileName := 'oracle-primary-sig.gif';
    t_attachments(1).contentID := '123456789';
    t_attachments(1).fileBLOB := base64_decode(tmpClob);
    mam_email.setMailserver('my.mailserver.example.com');
    mam_email.send(
         'recipient@example.com'-- Recipient
        ,'cc@example.com'-- Recipient of copy
        ,'bcc@example.com'-- Recipient of blind copy
        ,'sender@example.com'-- Sender
        ,'Testmail with inline image'-- Subject
        ,'Plain Text Body'
        ,'<h1>Inline Image </h1>'||CHR(10)||
         '<p>Oracle Logo </p>
         <p><img src="cid:123456789"></p>'
        ,t_attachments
        );
END;

Download

mam_email.zip zipped file 5 KB
mam_email_test.sql Test skript 3 KB

Disclaimer

The software is not intended for blindfolded use in productive environments. If you do so be sure to test thoroughly because I will not give support in any case. I don't claim that this software is free of errors.

Licence

http://unlicense.org/ This is free and unencumbered software released into the public domain.

oracle@matzberger.de

Impressum