3 Replies Latest reply on Dec 10, 2013 12:27 AM by Mike Kutz

    Not Able to Send Mail in Oracle Apex

    Sudhir_Meru

      Hello Apex Experts,

       

        I am using Amazon Cloud  with details as using Oracle Apex 4.1 and Oracle 11g as database using Glass Fish Server.

       

      Problem am facing is E-Mail are not working. I tried adding our company smtp address but still it is not working.

       

      In Oracle Apex Admin scree Under Manage Instance --> Instance Settings --> Email tryed adding ( localhost and 10.1.1.100 SMT address ) this didnt work

       

      I am using below code to send mail 

      Begin

       

      -- Send plain text email

        APEX_MAIL.SEND(

          P_TO       =>  'sudhirn@data.com',

          P_FROM     =>  'sudhirn@data.com',

          P_CC       =>  'sudhirn@data.com',

          P_BCC      =>  'sudhirn@data.com',

          P_SUBJ     => 'test',

          P_BODY     => 'test');

       

         apex_mail.push_queue('10.1.1.100', 25);

       

      End;

       

      SELECT * FROM APEX_MAIL_QUEUE;

       

      I get ORA-24247: network access denied by access control list (ACL) . I also see last_updated as SYS in the log

       

      Please suggest me how to fix this issue.

       

      Thanks

      Sudhir

        • 1. Re: Not Able to Send Mail in Oracle Apex
          Mike Kutz

          Try THIS LINK

           

          (forgive me if I sound rude, this is the 5th time someone posted this question since Friday.)

          This is a Database problem, not an APEX problem.

          You are at the mercy of the DBA.

          The DBA must create an Access Control List [ACL] that allows your parsing schema to create a TCP connection to your mail server over the appropriate email port(s).

          (This is also true for FTP, twitter,web-services,etc. with similar requirements for Active Directory, LDAP, etc.)

           

          If the cloud service provider does not grant you DBA privileges, you must ask them to create the ACL for you.

           

          MK

          • 2. Re: Not Able to Send Mail in Oracle Apex
            Sudhir_Meru

            Hi Mike,

             

              Thanks for your reply, Let me give you a detail on where we are stuck on send mail.

             

               We have a Schema called PORTAL

             

              We are able to send mail from UTL_SMTP as mentioned below in a procedure

             

            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;


            Begin

            send_mail('sudhir@data.com','sudhir@data.com','test','10.0.0.100','25');

            End;


            The above code works i am able to send and receive mails.

             

            We also create ACL as mentioned below

             

            begin

             

             

            dbms_network_acl_admin.create_acl (

             

             

            acl => 'http_permission.xml', -- or any other name

             

             

            description => 'HTTP Access',

             

             

            principal => 'APEX_040100', -- the user name trying to access the network resource

             

             

            is_grant => TRUE,

             

             

            privilege => 'connect',

             

             

            start_date => null,

             

             

            end_date => null

             

             

            );

             

             

            end;

             

             

            /

             

             

            commit;

             

             

            SELECT * FROM dba_network_acl_privileges where principal='APEX_040100';

             

             

             

             

             

             

            begin

             

             

            DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permission.xml',

             

             

            principal => 'APEX_040100',

             

             

            is_grant => true,

             

             

            privilege => 'connect');

             

             

            end;

             

             

            /

             

             

             

             

            commit;

             

             

             

             

            begin

             

             

            DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permission.xml',

             

             

            principal => 'APEX_040100',

             

             

            is_grant => true,

             

             

            privilege => 'resolve');

             

             

            end;

             

             

            /

             

             

            commit;

             

             

             

             

             

             

            BEGIN

             

             

            dbms_network_acl_admin.assign_acl (

             

             

            acl => 'http_permission.xml',

             

             

            host => '10.1.1.100', /*can be computer name or IP , wildcards are accepted as well for example - '*.us.oracle.com'*/

             

             

            lower_port => 80,

             

             

            upper_port => 80

             

             

            );

             

             

            END;

             

             

            But still we are getting same ACL Error Please suggest US

             

            ORA-24247: network access denied by access control list (ACL)

             

            Thanks

            Sudhir

            • 3. Re: Not Able to Send Mail in Oracle Apex
              Mike Kutz

              cool. you got UTL_SMTP to work.

               

              code notes for your definition:

              Your ACL for APEX doesn't match what you tested.

              server name is different ( 10.0.0.100 vs 10.1.1.100 )

              and your ports are different ( 25 vs 80 )

               

              The 2nd anonymous block is not needed as the creation of the ACL (1st block) implies the 2nd.

              3rd anonymous block is not needed.  I don't feel like looking up what 'resolve' does but I don't use it in my setup.

               

              The 4th block is the biggest one.

              The server name and port you specify here must match identically to what you used to configure APEX.

              ( 'HOST' might even be case-sensitive)

              I don't know of any e-mail server that accept SMTP connections over port 80.

               

              Since you have a working ACL (for PORTAL):

              I'd probably cheat and just add APEX_0401000 to that ACL.

              (yes, you can have multiple 'principals'/schemas assigned to the same ACL)

              (yes, you can have multiple hosts assigned to the same ACL)

               

               

              MK