Introduction

I have been asked to do the steps to send mail from inside an Oracle 11G. There is a lot of good notes about that already on the web which i used to get the picture and to get things done. Still to me this post offers a nice overview.

On the Linux level:

## I found this: smtp.mb-nl.internal.mycompany.com as mail server.

## By checking /etc/mail/sendmail.cf
## I found:
#DS[mi.mycompany.com]
DS[smtp.mb-nl.internal.mycompany.com]

## So that is my first clue

Below are the steps you will need to do in the database.

## In order to send mails in Oracle 11g you will need to take care of several steps.

## They are not default installed in the database (11g). You need to actually install the UTL_MAIL package.

sqlplus /nolog
connect / as sysdba
@?/rdbms/admin/utlmail.sql
@?/rdbms/admin/prvtmail.plb
grant execute on utl_mail to public;

## Next, add the address & port of the e-mail server (smtp server).

## Hint: If you do not do this, you will receive “ORA-06502: PL/SQL: numeric or value error” when you try to use the UTL_MAIL package.

## Execute the following with user SYS as SYSDBA:

alter system set smtp_out_server = smtp.mb-nl.internal.mycompany.com:25' scope=both;

## check what is out there in the database.

select * from dba_network_acls;

## Dropping same named access_control list if already existing.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
      acl => 'mail_access.xml');
END;
/

## Create an access control list:

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'mail_access.xml',
    description  => 'Permissions to access e-mail server.',
    principal    => 'PUBLIC',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
END;
/

## Assign the list to the smtp ( mail server ).

## Note Default port is 25!

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'mail_access.xml',
    host         => 'smtp.mb-nl.internal.mycompany.com',
    lower_port   => 25,
    upper_port   => 25
    );
   COMMIT;
END;
/

## This is an example to check if it is working:

CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_message   IN VARCHAR2,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);
  UTL_SMTP.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.quit(l_mail_conn);
END;

/The code below shows how the procedure is called.

 BEGIN
  send_mail(p_to        => 'mathijs.bruggink@mycompany.com',
            p_from      => 'oracle@mbnld33r.com',
            p_message   => 'This is a test message.',
            p_smtp_host => 'smtp.mb-nl.internal.mycompany.com');
END;
/

## After Feedback  that public indeed should be avoided in these matters this is the adepted example:

IF you set it up not for public but for dedicated users.

-- M Bruggink
-- Enabling  Mail functionality in Oracle
-- 20131024

spool 20131024.lst
connect / as sysdba;

@?/rdbms/admin/utlmail.sql
@?/rdbms/admin/prvtmail.plb
grant execute on utl_mail to public;

alter system set smtp_out_server = 'smtp.MB-nl.internal.MYCOMPANY.com:25' scope=both;

BEGIN
   DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
      acl => 'mail_access.xml');
END;
/

--Create an access control list:
BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'mail_access.xml',
    description  => 'Permissions to access e-mail server.',
    principal    => 'SYS',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
 END;
/

-- Assign the list to the smtp ( mail server ):
-- Note Default port is 25!

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'mail_access.xml',
    host         => 'smtp.MB-nl.internal.MYCOMPANY.com',
    lower_port   => 25,
    upper_port   => 25
    );
   COMMIT;
 END;
/
BEGIN
   DBMS_NETWORK_ACL_ADMIN.add_privilege (
    acl          => 'mail_access.xml',
    principal    => 'SYSTEM',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
 END;
/

BEGIN
   DBMS_NETWORK_ACL_ADMIN.add_privilege (
    acl          => 'mail_access.xml',
    principal    => 'SCOTT',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
 END;
/

BEGIN
   DBMS_NETWORK_ACL_ADMIN.add_privilege (
    acl          => 'mail_access.xml',
    principal    => 'ARNOLD',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
 END;
/
-- check the setup
COLUMN host FORMAT A30
COLUMN acl FORMAT A30

SELECT host, lower_port, upper_port, acl
FROM   dba_network_acls;

COLUMN acl FORMAT A30
COLUMN principal FORMAT A30
set lines 200

SELECT acl,
       principal,
       privilege,
       is_grant,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   dba_network_acl_privileges;
spool off

Useful notes in Mos:

NOTE:74269.1 – How to Test an SMTP Mail Gateway From a Command Line Interface

NOTE:130785.1 – How To Send E-mail Using UTL_SMTP

Happy reading,

Mathijs