The UTL_MAIL.SEND_ATTACH_RAW procedure in Oracle PL/SQL is part of the UTL_MAIL package, which provides an interface for sending emails from within the Oracle Database. This procedure allows you to send an email with an attachment that is passed as raw binary data. It is particularly useful for attaching non-text files, such as images, PDFs, or any binary file formats.
Basic Syntax of UTL_MAIL.SEND_ATTACH_RAW
UTL_MAIL.SEND_ATTACH_RAW ( sender IN VARCHAR2, recipients IN VARCHAR2, cc IN VARCHAR2 DEFAULT NULL, bcc IN VARCHAR2 DEFAULT NULL, subject IN VARCHAR2, message IN VARCHAR2, attachment IN RAW, att_mime_type IN VARCHAR2, att_filename IN VARCHAR2, mime_type IN VARCHAR2 DEFAULT 'text/plain; charset=us-ascii', priority IN PLS_INTEGER DEFAULT 3 );
Parameters
sender: The email address of the sender.
recipients: A comma-separated list of recipient email addresses.
cc: A comma-separated list of CC (Carbon Copy) recipient email addresses (optional).
bcc: A comma-separated list of BCC (Blind Carbon Copy) recipient email addresses (optional).
subject: The subject of the email.
message: The body of the email.
attachment: The attachment data in raw format (a RAW datatype in Oracle).
att_mime_type: The MIME type of the attachment, e.g., ‘application/pdf’ for a PDF file, ‘image/jpeg’ for a JPEG image, etc.
att_filename: The filename for the attachment (what the recipient will see).
mime_type: The MIME type for the email message body, defaulting to text/plain.
priority: The priority of the email, where lower values represent higher priority (optional).
Key Points
Attachment as Binary (RAW): The key distinction of SEND_ATTACH_RAW is that the attachment is passed as a RAW datatype, which is useful for handling binary data like images, PDFs, or other binary files.
Attachment MIME Type: You must specify the MIME type of the attachment using the att_mime_type parameter. This ensures that the email client will interpret the attachment correctly. For instance, application/pdf for PDF files, image/jpeg for JPEG images, etc.
Attachment Filename: The att_filename parameter lets you define the name that will be displayed to the recipient for the attached file.
MIME Type of Message: The mime_type parameter specifies the MIME type of the email body. By default, it’s set to text/plain, but you can change it to text/html if you’re sending an HTML-formatted email.
SMTP Configuration: Before using the UTL_MAIL package, the Oracle instance must be properly configured with an SMTP server. This is done by setting the SMTP_OUT_SERVER parameter in the Oracle initialization file (init.ora).
Privileges and Setup: To use the UTL_MAIL package, you need to ensure that:
The UTL_MAIL package is installed (it is not installed by default in some Oracle versions).
The Oracle user has the necessary privileges to use the UTL_MAIL package.
The database administrator has configured the mail server using the SMTP_OUT_SERVER parameter.
Example
Below is an example of using UTL_MAIL.SEND_ATTACH_RAW to send an email with a PDF attachment:
DECLARE
l_attachment RAW(32767);
BEGIN
-- Assuming the attachment data is retrieved from a table or file
SELECT file_data INTO l_attachment FROM my_file_table WHERE file_id = 1;
UTL_MAIL.SEND_ATTACH_RAW(
sender => '[email protected]',
recipients => '[email protected]',
subject => 'Test Email with Attachment',
message => 'Please find the attached PDF document.',
attachment => l_attachment,
att_mime_type => 'application/pdf',
att_filename => 'test_document.pdf'
);
END;
Common Use Cases
Sending PDFs or Reports: Automating the sending of generated reports in PDF format to business users or clients.
Sending Binary Files: Distributing image files, logs, or other binary data directly from the database.
Email Notifications: Automating system-generated emails with logs or reports attached.
Conclusion
The UTL_MAIL.SEND_ATTACH_RAW procedure is a powerful tool for sending emails with attachments in Oracle PL/SQL, especially when dealing with binary files. However, careful attention must be paid to the database setup and SMTP configuration to ensure successful email delivery.