UTL_MAIL sends an e-mail in one procedure call: sender, recipients, subject, and message, with optional copies and an attachment. It hides the SMTP conversation that UTL_SMTP makes you write line by line. It is not installed by default, so a DBA installs it first.
Code for This Guide
The main examples are in the examples/pkg-files-network folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
utl_mail.send(sender, recipients, cc => null, bcc => null,
subject => null, message => null,
mime_type => 'text/plain; charset=us-ascii',
priority => 3, replyto => null);
utl_mail.send_attach_varchar2(..., attachment => text, att_mime_type => ...,
att_filename => ...);
utl_mail.send_attach_raw(..., attachment => raw_bytes, ...);Several recipients are separated by commas. The mail server comes from the SMTP_OUT_SERVER parameter, and the connection needs an access control entry with the connect privilege.
Install UTL_MAIL
As SYS, in the pluggable database, run the two installation scripts from the Oracle home and grant the package:
Example:
@?/rdbms/admin/utlmail.sql @?/rdbms/admin/prvtmail.plb grant execute on utl_mail to nimbus;
Output:
Package UTL_MAIL compiled SYNONYM UTL_MAIL created. Package UTL_MAIL_INTERNAL compiled Package Body UTL_MAIL_INTERNAL compiled Grant succeeded. Package Body UTL_MAIL compiled No errors. Grant succeeded.
Send Mail and an Attachment
The examples send to a local test SMTP server, setup/netlab/server.mjs in the repository, which accepts every message, delivers nothing, and prints each one in its window.
Example:
alter session set smtp_out_server = 'host.docker.internal:2525';
begin
utl_mail.send(sender => 'ops@nimbus.example',
recipients => 'crew.dxb@nimbus.example',
cc => 'duty.manager@nimbus.example',
subject => 'NM150 gate change',
message => 'NM150 now departs from gate B12.');
utl_mail.send_attach_varchar2(
sender => 'ops@nimbus.example',
recipients => 'crew.dxb@nimbus.example',
subject => 'Morning departures',
message => 'The list is attached.',
attachment => 'NM113,ORD,01:05' || chr(10) || 'NM101,LHR,02:05',
att_mime_type => 'text/csv',
att_filename => 'departures.csv');
end;
/Output:
Session altered. PL/SQL procedure successfully completed.
The session points SMTP_OUT_SERVER to the test server. The first call sends a gate change with a copy to the duty manager; the second attaches a small CSV file built from text.
Things to Know
- A DBA usually sets SMTP_OUT_SERVER once for the database with ALTER SYSTEM.
- For non-ASCII text, set MIME_TYPE to 'text/plain; charset=utf-8', or 'text/html; charset=utf-8' for HTML mail.
- UTL_MAIL has no login for authenticated mail servers; use UTL_SMTP for those, or a local relay.
Related Guides
Conclusion
UTL_MAIL sends e-mail with copies and attachments in one call once a DBA installs it and sets SMTP_OUT_SERVER. Use it for notifications from PL/SQL, with a UTF-8 MIME type for international text, and UTL_SMTP when the mail server requires a login.
See my articles more often when you search Google.
