Oracle 10g includes a new package, the UTL_MAIL, that eliminates much of the extra work that the earlier package, UTL_SMTP required. It is an additional layer to UTL_SMTP, which is still available as well. This new package makes it possible for a PL/SQL programmer to send programatically composed emails from the database. It requires only the normal mental model of a user of a GUI email client rather than an understanding of the underlying protocol (SMTP) features.
The UTL_MAIL package is a utility for managing email which includes commonly used email features, such as attachments, CC, BCC, and return receipt. UTL_MAIL is not installed by default because of the SMTP_OUT_SERVER configuration requirement and the security exposure this involves. In installing UTL_MAIL, you should take steps to prevent the port defined by SMTP_OUT_SERVER being swamped by data transmissions.
You must install UTL_MAIL and define the SMTP_OUT_SERVER. First you must connect to the database as user SYS and run the scripts
-> Open sqlplus and the command to connect is ‘/ as sysdba’
Step1
The package UTL_MAIL is loaded by running the following scripts:
SQL> @$ORACLE_HOME\rdbms\admin\utlmail.sql
SQL> @$ORACLE_HOME\rdbms\admin\prvtmail.plb
If windows OS:
SQL> @%ORACLE_HOME%\RDBMS\ADMIN\utlmail.sql
SQL> @%ORACLE_HOME%\RDBMS\ADMIN\prvtmail.plb
Step2
You define the SMTP_OUT_SERVER parameter to identify the SMTP server in the init.ora RDBMS initialization file. However, if SMTP_OUT_SERVER is not defined, this invokes a default of DB_DOMAIN which is guaranteed to be defined to perform appropriately.
SMTP_OUT_SERVER parameter must be configured as:
SQL> alter system set smtp_out_server = ‘SMTP_SERVER_IP_ADDRESS[:SMTP_PORT]’ scope=both;
Note:
25 = Default SMTP Port
The Scope is of 3 types as below
1. memory (for the current Session only)
2. spfile (permanently – but need to restart the server)
3. both
Step3
To enable other Database Users or the PUBLIC to use this functionality, grant execute permission on UTL_MAIL package.
GRANT execute ON utl_mail TO
(or)
GRANT execute ON utl_mail TO PUBLIC;
Thats It, your database is configured to send emails
Procedures in UTL_MAIL Package:
Three procedures are available in the UTL_MAIL package which enables us to send an email with or without attachment.
Procedure 1: SEND
This procedure packages an email message into the appropriate format, locates SMTP information, and delivers the message to the SMTP server for forwarding to the recipients. This is a single stored procedure which sends email to the recipients in the list. It hides the SMTP API and exposes a one-line email facility for ease of use.
The syntax is:
UTL_MAIL.SEND (
sender IN VARCHAR2 CHARACTER SET ANY_CS,
recipients IN VARCHAR2 CHARACTER SET ANY_CS,
cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
message IN VARCHAR2 CHARACTER SET ANY_CS,
mime_type IN VARCHAR2 DEFAULT 'text/plain; charset=us-ascii',
priority IN PLS_INTEGER DEFAULT NULL);
where
sender: The email address of The sender.
recipients: The email addresses of The recipient(s), separated by commas.
cc: The email addresses of The CC recipient(s), separated by commas, default NULL.
bcc: The email addresses of The BCC recipient(s), separated by commas, default NULL.
subject: A string to be included as email subject string, default NULL.
message: A text message body.
mime_type: The mime type of the message, default is 'text/plain; charset=us-ascii'.
priority: The message priority, default is NULL.
Example:
CREATE OR REPLACE TRIGGER SCOTT.db_shutdown before shutdown on database
BEGIN
UTL_MAIL.send(sender => 'me@address.com', recipients => 'you@address.com', cc => 'person1@address.com', 'person2@address.com', bcc => 'person3@address.com', 'person4@address.com', subject => 'DB Shutdown', message => 'This email means db is shutting down', mime_type => 'text; charset=us-ascii', priority => 3);
END;
/
Procedure 2: SEND_ATTACH_RAW
This is yet another procedure available in the UTL_MAIL package, using which any raw attachments can be sent in the email. (i.e.) This procedure is the SEND Procedure overloaded for RAW attachments.
The Syntax is:
UTL_MAIL.SEND_ATTACH_RAW (
sender IN VARCHAR2 CHARACTER SET ANY_CS,
recipients IN VARCHAR2 CHARACTER SET ANY_CS,
cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
message IN VARCHAR2 CHARACTER SET ANY_CS,
mime_type IN VARCHAR2 DEFAULT 'text/plain; charset=us-ascii',
priority IN PLS_INTEGER DEFAULT NULL
attachment IN RAW,
att_inline IN BOOLEAN DEFAULT TRUE,
att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'application/octet',
att_filename IN VARCHAR2 DEFAULT NULL);
where
attachment: A RAW attachment.
att_inline: Specifies whether the attachment is viewable inline with the message body, default is TRUE.
att_mime_type: The mime type of the attachment, default is ‘application/octet’.
att_filename: The string specifying a filename containing attachment,default NULL.
Example:
DECLARE
rfile RAW (32767) := HEXTORAW(‘616262646566C2AA’);
BEGIN
UTL_MAIL.SEND_ATTACH_RAW(sender => 'me@address.com', recipients => 'you@address.com', cc => 'person1@address.com', 'person2@address.com', bcc => 'person3@address.com', 'person4@address.com', subject => 'Test Mail', message => 'Hello World', mime_type => 'text; charset=us-ascii', priority => 3, attachment => rfile, att_inline => FALSE, att_mime_type=> 'application/octet', att_filename => 'filename.pdf');
END;
/
Procedure 3: SEND_ATTACH_VARCHAR2
This procedure is the SEND Procedure overloaded for VARCHAR2 attachments.
The Syntax is same as that of SEND_ATTACH_RAW Procedure where the attachment is a TEXT attachment.
Example:
DECLARE
rfile VARCHAR2(255) := ‘This is a sample of a VARCHAR2 attachment!’;
BEGIN
SEND_ATTACH_VARCHAR2(sender => 'me@address.com', recipients => 'you@address.com', cc => 'person1@address.com', 'person2@address.com', bcc => 'person3@address.com', 'person4@address.com', subject => 'Test Mail', message => 'Hello World', mime_type => 'text; charset=us-ascii', priority => 3, attachment => rfile, att_inline => FALSE, att_mime_type=> 'application/octet', att_filename => 'filename.pdf');
END;
/
Posts
