The UTL_MAIL.SEND procedure in Oracle PL/SQL is part of the UTL_MAIL package, which is used to send emails directly from within an Oracle database. It provides a simple interface for email messaging, making it easier for developers to automate notifications, alerts, or reports from PL/SQL applications.
Overview of UTL_MAIL.SEND
The UTL_MAIL.SEND procedure sends an email message to one or more recipients. It supports standard email fields such as from, to, cc, bcc, subject, and message, and allows attachments. To use it, the UTL_MAIL package must be installed and configured, and the database must be properly set up to communicate with an SMTP server.
Here’s the basic syntax:
UTL_MAIL.SEND(
sender IN VARCHAR2,
recipients IN VARCHAR2,
cc IN VARCHAR2 DEFAULT NULL,
bcc IN VARCHAR2 DEFAULT NULL,
subject IN VARCHAR2 DEFAULT NULL,
message IN VARCHAR2 DEFAULT NULL,
mime_type IN VARCHAR2 DEFAULT 'text/plain; charset=us-ascii'
);
Parameters
sender: The email address of the sender.
recipients: A comma-delimited list of email addresses for the main recipients.
cc (optional): A comma-delimited list of email addresses for carbon copy recipients.
bcc (optional): A comma-delimited list of email addresses for blind carbon copy recipients.
subject (optional): The subject of the email.
message (optional): The body content of the email message.
mime_type (optional): Specifies the MIME type for the message. The default is ‘text/plain; charset=us-ascii’.
Example
BEGIN
UTL_MAIL.SEND(
sender => '[email protected]',
recipients => '[email protected]',
cc => '[email protected]',
bcc => NULL,
subject => 'Test Email from Oracle PL/SQL',
message => 'Hello, this is a test email sent from PL/SQL!',
mime_type => 'text/plain; charset=utf-8'
);
END;
/
In this example:
The email is sent from [email protected].
The recipient is [email protected].
A carbon copy is sent to [email protected].
The email subject is “Test Email from Oracle PL/SQL.”
The message body is “Hello, this is a test email sent from PL/SQL!”
Setting Up UTL_MAIL
Before using UTL_MAIL.SEND, some configurations are necessary:
Install the UTL_MAIL package: If UTL_MAIL is not already installed, run the following scripts:
$ORACLE_HOME/rdbms/admin/utlmail.sql $ORACLE_HOME/rdbms/admin/prvtmail.plb
Grant necessary privileges: Grant execution privileges on the UTL_MAIL package to the relevant users:
GRANT EXECUTE ON UTL_MAIL TO my_user;
Set the SMTP server: The database must be configured with an SMTP server for sending emails. Set the SMTP_OUT_SERVER parameter:
ALTER SYSTEM SET smtp_out_server = 'smtp.example.com';
You can also set it dynamically in the PL/SQL block if needed.
Error Handling
UTL_MAIL.SEND can raise exceptions if there is a failure in sending the email. Common issues include:
Incorrect SMTP server configuration.
Invalid email addresses.
Network issues.
Here’s an example of error handling:
BEGIN
UTL_MAIL.SEND(
sender => '[email protected]',
recipients => '[email protected]',
subject => 'Test Email',
message => 'This is a test email.'
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error sending email: ' || SQLERRM);
END;
/
Notes
UTL_MAIL is available in Oracle 10g and later.
You must ensure that the SMTP_OUT_SERVER is properly configured.
Oracle recommends using UTL_SMTP for more advanced email functionality, such as adding attachments with specific MIME types.
Conclusion
The UTL_MAIL.SEND procedure is a convenient way to send emails from Oracle PL/SQL. It’s straightforward to use for sending basic text messages but requires some configuration for the database to interact with an SMTP server. For more complex email functionality (like adding multiple attachments), Oracle provides the UTL_SMTP package.