The UTL_MAIL.SEND_ATTACH_VARCHAR2 procedure in Oracle PL/SQL is part of the UTL_MAIL package, which facilitates sending emails from the database using SMTP. The SEND_ATTACH_VARCHAR2 procedure specifically allows for sending an email with an attachment that is in VARCHAR2 format. It can be used to send text-based attachments such as logs or reports directly from a PL/SQL block.
Syntax
UTL_MAIL.SEND_ATTACH_VARCHAR2 ( sender IN VARCHAR2, recipients IN VARCHAR2, cc IN VARCHAR2 DEFAULT NULL, bcc IN VARCHAR2 DEFAULT NULL, subject IN VARCHAR2, message IN VARCHAR2, attachment IN VARCHAR2, att_inline IN BOOLEAN DEFAULT FALSE, att_filename IN VARCHAR2 DEFAULT NULL, mime_type IN VARCHAR2 DEFAULT 'text/plain', priority IN PLS_INTEGER DEFAULT 3 );
Parameters
sender: The email address of the sender (e.g., ‘[email protected]’).
recipients: A comma-delimited list of recipient email addresses.
cc (optional): A comma-delimited list of addresses to send a carbon copy.
bcc (optional): A comma-delimited list of addresses to send a blind carbon copy.
subject: The subject line of the email.
message: The body text of the email.
attachment: The content of the attachment, provided as a VARCHAR2 string.
att_inline (optional): A Boolean flag indicating if the attachment should be displayed inline (TRUE) or as a regular file (FALSE). Default is FALSE.
att_filename (optional): The name of the attachment file (e.g., ‘report.txt’). If not specified, no filename is attached.
mime_type (optional): The MIME type of the attachment (default is ‘text/plain’ for text files).
priority (optional): The email priority (1 for high, 3 for normal, 5 for low). Default is 3.
Example
Here’s a simple example where an email with a text-based attachment is sent using this procedure:
BEGIN
UTL_MAIL.SEND_ATTACH_VARCHAR2(
sender => '[email protected]',
recipients => '[email protected], [email protected]',
subject => 'Monthly Report',
message => 'Please find attached the monthly report.',
attachment => 'This is the content of the report...',
att_filename => 'report.txt',
mime_type => 'text/plain'
);
END;
/
Notes
SMTP Configuration: The UTL_MAIL package relies on the Oracle database’s SMTP setup. Ensure that SMTP is correctly configured using the SMTP_OUT_SERVER initialization parameter to point to your mail server.
Privileges: The database user invoking this procedure must have the necessary privileges, and the UTL_MAIL package must be installed (usually not installed by default).
Attachment Size: Since the attachment is a VARCHAR2, its size is limited by the maximum length of a VARCHAR2 in PL/SQL (32,767 bytes in PL/SQL).
Error Handling: Exceptions such as UTL_MAIL.INVALID_ADDRESS and UTL_MAIL.INVALID_ATTACHMENT may occur, and proper exception handling should be implemented.
This procedure is useful in situations where text-based reports or logs need to be sent as attachments via email directly from the database.