Skip to Main Content
  • Questions

  • Emails sending out of 12c SuperCluster Database

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jaydeepsinh.

Asked: February 26, 2019 - 9:25 pm UTC

Last updated: March 01, 2019 - 1:12 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

Oracle version:


SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 26 15:14:07 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Feb 26 2019 14:05:04 -06:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL>


I am currently working on writing a stored procedure to send out emails from our new Oracle 12c SuperCluster Database to our SMTP exchange server:

PL/SQL Code:

  create or replace PROCEDURE mail_sc (sender     IN VARCHAR2,
   recipients IN VARCHAR2,
   subject    IN VARCHAR2,
   message    IN VARCHAR2) IS
    conn utl_smtp.connection;
  BEGIN
     conn := UTL_SMTP.OPEN_CONNECTION('Exchange server hostname'); 
         
       UTL_SMTP.HELO(conn, 'exchange server hostname');
       UTL_SMTP.MAIL(conn, 'sender_email');
       UTL_SMTP.RCPT(conn, 'recipient_email');
       UTL_SMTP.OPEN_DATA(conn);
       UTL_SMTP.WRITE_DATA(conn, UTL_TCP.CRLF || 'Hello, world!');
       UTL_SMTP.CLOSE_DATA(conn);
       UTL_SMTP.QUIT(conn);
   END;


execute MAIL_SC('sender_email','recipient_email','test test','Test Message');


After executing store procedure I ended up receiving errors like:

Error report -
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 19
ORA-06512: at "SYS.UTL_TCP", line 280
ORA-06512: at "SYS.UTL_SMTP", line 163
ORA-06512: at "SYS.UTL_SMTP", line 199
ORA-06512: at "User_name.MAIL_SC", line 7
ORA-06512: at line 1
24247. 00000 -  "network access denied by access control list (ACL)"
*Cause:    No access control list (ACL) has been assigned to the target
           host or the privilege necessary to access the target host has not
           been granted to the user in the access control list.
*Action:   Ensure that an access control list (ACL) has been assigned to
           the target host and the privilege necessary to access the target
           host has been granted to the user.


Is there a simple way to write PL/SQL Code that communicates with SMTP server via stored procedures that sends email to recipients after successfully completion of stored procedure run.

I am a newbie to 12c environment and setting up the SMTP server on our new 12c

Any help is truly appreciated.

Jay

and Chris said...

You're blocked by the Access Control List (ACL). These control who has network access from the database and on which ports.

See:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9529251800346874328
https://oracle-base.com/articles/12c/fine-grained-access-to-network-services-enhancements-12cr1

For examples on how to use this.

Also, it's easier to use UTL_MAIL to send emails from the database than UTL_SMTP:

https://oracle-base.com/articles/10g/utl_mail-send-email-from-the-oracle-database
https://asktom.oracle.com/mobile/pls/asktom/asktom.search?tag=sending-mail-using-utl-mail

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Jay Parmar, February 27, 2019 - 3:38 pm UTC

Thanks Chris Sixon.

Documents you have sent are useful however to test the error do I need to contact DBA and/or Network experts and/or Outlook Exchange server admins.

To my knowledge database server is white listed on Exchange server .

Also, Is there a way to Test an SMTP Mail Gateway From a Command Line Interface from Database Server before testing PL/SQL ??

Any Advise is sincerely appreciated

Thanks
Jay
Chris Saxon
February 27, 2019 - 4:09 pm UTC

You still need to configure the ACLs inside your database. The ACLs are designed to stop code in your database talking to other servers unless given permission to do so.

The error you're getting relates to the ACLs. So you need to sort this out, regardless of the state of firewalls etc.

So you're probably going to need the DBA to run the scripts to open the ACL for you. It depends on whether you've got access to execute DBMS_NETWORK_ACL_ADMIN.

Another stored Proc

Jay Parmar, February 27, 2019 - 6:27 pm UTC

Good Afternoon Chris Saxon,

I have written another stored proc to test UTL_SMTP configurations.
My DBA is saying fixed what I needed such as:
It seems that error, ORA-24247 , is common per Oracle’s document. I have fixed as per Oracle's Document.

“Using ACEs to Set Up ACLs on 12c (To Remedy ORA-24247 Errors) (Doc ID 2267848.1)”

New Stored Proc:

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;

execute send_mail(p_to => 'recipient_email',p_from => 'sender_email',p_message => 'This is a test message.',p_smtp_host => 'SMTP_HOST_NAME',p_smtp_port=>25);

PL/SQL procedure successfully completed.

The procedure completes successfully while it is not sending out email.

Not sure why??

Connor McDonald
February 28, 2019 - 1:56 am UTC

If the procedure completes successfully, it means we successfully passed this message to the email server. That is not a guarantee that the email server will send it etc. You'd need to check the email server logs and/or speak to the admin for the email server to diagnose further.

If you set a "from" address, then typically a rejected email will come back to that address with details as to why it could not be sent.

My question is answered

Jaydeepsinh Parmar, February 28, 2019 - 4:39 pm UTC

Thanks Chris for your help.

You were right It was ACL control lists were blocking connectivity from Database server into Exchange server.

Made my day!!!!

Connor McDonald
March 01, 2019 - 1:12 am UTC

Glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library