UTL_MAIL
The UTL_MAIL package is an interesting addition to the database functionality because it came after UTL_SMTP and could have been added to the existing functionality, but was instead split off into its own package. The package is not installed by default either. If you wish to use UTL_MAIL, your DBA will need to run the following two scripts:
$ORACLE_HOME/rdbms/admin/utlmail.sql $ORACLE_HOME/rdbms/admin/prvtmail.plb
You will also need to set the SMTP_OUT_SERVER system parameter to a comma-delimited list of at least one SMTP host, and optionally ports if not using the default port of 25. If SMTP_OUT_SERVER is not set (i.e. NULL,) the package is supposed to default to the DB_DOMAIN parameter, however most, if not all, versions (up through at least 21.3) have a bug in them preventing the db_domain defaulting from working. If you are using an old version of the database (11gR1 or earlier,) the SMTP_OUT_SERVER parameter is not session or system modifiable. You DBA will need to set the parameter and then restart the instance to apply the change.
And last, the DBA will then need to grant the EXECUTE privilege on the package to users either directly or through a role.
Strangely, and erroneously, comments within the script for the package specification mention the following two parameters which do not exist and, thankfully, are not referenced by the package.
** SMTP_SERVER=my_server.my_company.com
** SMTP_DOMAIN=my_company.com
These comment errors also exist up through at least version 21.3.
The package has had only a few changes since its 10.1 introduction. Version 11.1 changed the package to use INVOKER Rights. Version 11.2 introduced the REPLYTO parameter to each procedure.
There are three procedures in the package, all following a similar API.
- UTL_MAIL.SEND – sends a single text message of up to 32K (the varchar2 limit)
- UTL_MAIL.SEND_ATTACH_RAW – sends a single text message up to 32K with the option to include a RAW attachment of up to 32K bytes.
- UTL_MAIL.SEND_ATTACH_VARCHAR2 sends a single text message up to 32K with the option to include a text attachment of up to 32K.
Most of the parameters are the same for all three procedures. The highlighted rows below, as their names imply, only apply to the procedures that send an attachment.
| sender | Email address of the sender |
| recipients | Email addresses of the recipient(s), separated by commas |
| cc | Email addresses of the CC recipient(s), separated by commas, default is NULL |
| bcc | Email addresses of the BCC recipient(s), separated by commas, default is NULL |
| subject | String to be included as email subject string, default is NULL |
| message | Text message body |
| mime_type | Mime type of the message, default is ‘text/plain; charset=us-ascii’ |
| priority | Message priority, which maps to the X-priority field. 1 is the highest priority and 5 the lowest. The default is 3. |
| attachment | RAW or VARCHAR2 attachment |
| att_inline | Specifies whether the attachment is viewable inline with the message body, default is TRUE |
| att_mime_type | Mime type of the attachment, default is ‘application/octet’ for RAW or ‘text/plain; charset=us-ascii’ for VARCHAR2 |
| att_filename | String specifying a filename containing the attachment, default is NULL |
| replyto | Defines to whom the reply email is to be sent |
Once the installation, parameters, and grants are all set up, the usage is quite simple.
BEGIN
UTL_MAIL.send(
sender => 'sean.stuber@gmail.com',
recipients => 'john.doe@example.com,jane.doe@example.com',
subject => 'Test Subject',
message => 'Hello World!'
);
END;
Sending an attachment isn’t much harder. In the example below I’m pulling the source code of one of my functions into a single varchar2 variable and emailing it to a couple of recipients.
DECLARE
-- UTL_MAIL will allow attachments up to 32K
v_text VARCHAR2(32767);
BEGIN
-- Read the source code of one of my functions into a variable
-- I know this function will fit within 32K
FOR s IN (SELECT text
FROM user_source
WHERE name = 'SPLIT_CLOB'
AND type = 'FUNCTION'
ORDER BY line)
LOOP
v_text := v_text || s.text;
END LOOP;
UTL_MAIL.send_attach_varchar2(
sender => 'sean.stuber@gmail.com',
recipients => 'john.doe@example.com,jane.doe@example.com',
subject => 'Split Clob source attached',
message => 'I hope you find the attached file helpful.',
attachment => v_text,
att_inline => FALSE,
att_filename => 'split_clob_function.txt'
);
END;
To send a RAW file the general structure is the same as sending text. Gather your RAW content into a variable and then attach the variable. In this example I’m pulling a small image file (JPEG format) from a table and attaching it to my email message.
DECLARE
-- UTL_MAIL will allow attachments up to 32K
v_image RAW(32767);
BEGIN
-- Read the image file into variable
FOR s IN (SELECT rawimage
FROM my_images
WHERE id=1)
LOOP
v_text := v_text || s.text;
END LOOP;
UTL_MAIL.send_attach_raw(
sender => 'sean.stuber@gmail.com',
recipients => 'john.doe@example.com,jane.doe@example.com',
subject => 'sample image attached',
message => 'Attached is a simple stick figure.',
attachment => v_image,
att_inline => FALSE,
att_filename => 'sample.jpg'
);
END;
In both of the attachment examples I chose to render the attachment as a file. If instead I wanted the content to be embedded within the body of the email it would just be a matter of changing the att_inline parameter to TRUE. This will generally work ok for text inlining but for images or other RAW types, the final rendering will be dependent on the client software receiving the email.
Using other mime types such as ‘text/html’ will allow you to send formatted text but the message will be more complicated as you must construct valid html content so the client can render it and the formatting will be subject to the level of html support in the recipient’s client software.
The cc, bcc, and replyto parameters work as you would expect them to in standard email clients.
That’s pretty much all there is to UTL_MAIL. Its simplicity is its appeal; but that simplicity comes with some fairly obvious restrictions. Your message must be less than or equal to 32K in length. Your attachments can’t be larger than 32K either. You are also limited to only one attachment per email. The smtp servers used can not require authentication.
For these reasons, I generally prefer to use UTL_SMTP. Even though it requires additional work, UTL_SMTP is still not that hard, and you can always write a wrapping procedure to hide the complexity. In fact, if you look at ALL_DEPENDENCIES you can see UTL_MAIL uses UTL_SMTP, so it is just a wrapper anyway and you can build one yourself to suit your needs.