How to send mail from oracle database | UTL_MAIL

Applicable for databases 10G onwards

Prerequisites: Get the smtp mail server details from the administrator.

Steps that needs to be followed for sending mails from oracle database.

Step 1: Install UTL_MAIL package by running the below files as SYS user
$ORACLE_HOME/rdbms/admin/utlmail.sql
$ORACLE_HOME/rdbms/admin/prvtmail.plb

Step 2: Grant permissions to PUBLIC or to the respective users
SQL> GRANT EXECUTE ON UTL_MAIL TO PUBLIC;

Step 3: Set SMTP_OUT_SERVER parameter in the pfile/spfile
SQL> ALTER SYSTEM SET smtp_out_server='smtp.domain.com' SCOPE=both; 

Step 4: Create a test procedure to send email
CREATE OR REPLACE PROCEDURE test_email AS
BEGIN
  UTL_MAIL.send(sender => 'testuser1@domain.com',
            recipients => 'testuser2@domain.com',
    cc => 'testuser3@domain.com',
    bcc => 'testuser4@domain.com',
            subject => 'Test Mail',
            message => 'Hello World',
            mime_type => 'text; charset=us-ascii');
END;
/

Step 5: Execute Create a test procedure to send email
SQL> exec test_email; 

Note: You need to create ACL for the users who can send mail except sys user.

Steps as mentioned below:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl => 'utl_smtp.xml',
    description => 'Network Access Control for SYSTEM',
    principal => 'SYSTEM', -- here my user is system
    is_grant => TRUE,
    privilege => 'connect');
END;
/

begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => 'smtp.domain.com',
lower_port => 25);
commit;
end;
/

begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml',
principal => 'SYSTEM', -- here my user is system
is_grant => TRUE,
privilege => 'connect');
COMMIT;
end;

/

SELECT host, lower_port, upper_port, privilege, status FROM   user_network_acl_privileges;

SELECT host, lower_port, upper_port, acl FROM dba_network_acls ;