14 Replies Latest reply on Sep 17, 2018 11:49 AM by Omar Baig

    Email Through Oracle Apex 18.1

    Omar Baig

      Assalamoalikum,

      I want to send an email from my Apex Application, I am using Oracle Apex 18.1, and procedure are as follows:

       

        

         apex_mail.send(

            p_to        => 'omar-baig@hotmail.com',

            p_from      => 'obpersonal@gmail.com',

            p_body      => l_body,

            p_body_html => l_body_html,

            p_subj      => 'Activate Your Account');

       

      and I am getting this error

       

      ORA-29278: SMTP transient error: 421 Service not available

        • 1. Re: Email Through Oracle Apex 18.1
          cormaco

          Your mailserver can not be found.

          Have you configured the Apex email settings?

          https://docs.oracle.com/database/apex-18.1/AEADM/configuring-instance-settings.htm#AEADM210

           

          Configuring Email

          To enable Oracle Application Express to send mail, an instance administrator must configure instance-level email settings.

          • 2. Re: Email Through Oracle Apex 18.1
            Billy~Verreynne

            Omar Baig wrote:

             

            ORA-29278: SMTP transient error: 421 Service not available

            This means that a TCP connection to the configured mail (SMTP) server, failed.

             

            The IP address is invalid or no route to it found. The hostname did not resolve to an IP address. A firewall blocked the TCP connection. Etc.

             

            Easy way to test is using telnet, or nc (netcat), on the database server, to test whether the mail server is reachable. E.g.

            telnet mail.my-domain.com 25

            or:

            nc -v -w2 mail.my-domain.coom 25

             

            Newer versions of Linux do not install telnet client by default - consider installing and using nc in that case.

            • 3. Re: Email Through Oracle Apex 18.1
              Omar Baig

              I already create this Procedure from SYS user

               

              BEGIN

                DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'www.xml',

                                                  description => 'WWW ACL',

                                                  principal   => 'HR',

                                                  is_grant    => true,

                                                  privilege   => 'connect');

               

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

                                                     principal => 'HR',

                                                     is_grant  => true,

                                                     privilege => 'resolve');

               

                DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'www.xml',

                                                  host => '10.1.1.2',

                                                  lower_port => 25,

                                                  upper_port => 25);

              END;

              /

              COMMIT;

               

              but still ora-24247

              • 4. Re: Email Through Oracle Apex 18.1
                Omar Baig

                However when i use this procedure

                 

                DECLARE

                v_From      VARCHAR2(80) := 'omarbaig@mrgc.com.pk';

                v_Recipient VARCHAR2(80) := 'omar-baig@hotmail.com';

                v_Subject   VARCHAR2(80) := 'test subject';

                v_Mail_Host VARCHAR2(30) := '10.1.1.2';

                v_Mail_Conn utl_smtp.Connection;

                crlf        VARCHAR2(2)  := chr(13)||chr(10);

                BEGIN

                v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);

                utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);

                utl_smtp.Mail(v_Mail_Conn, v_From);

                utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);

                utl_smtp.Data(v_Mail_Conn,

                'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||

                'From: '   || v_From || crlf ||

                'Subject: '|| v_Subject || crlf ||

                'To: '     || v_Recipient || crlf ||

                crlf ||

                'some message text'|| crlf ||   -- Message body

                'more message text'|| crlf

                );

                utl_smtp.Quit(v_mail_conn);

                EXCEPTION

                WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then

                raise_application_error(-20000, 'Unable to send mail', TRUE);

                END;

                 

                instead of

                 

                declare

                   l_body          CLOB:= 'To view the content of this message, please use an HTML enabled mail client.'||utl_tcp.crlf;

                   l_body_html     CLOB;

                begin

                  

                   /* Send Activate Email */

                  

                   apex_mail.send(

                      p_to        => 'omar-baig@hotmail.com',

                      p_from      => 'omarbaig@mrgc.com.pk',

                      p_body      => l_body,

                      p_body_html => l_body_html,

                      p_subj      => 'Activate Your Account');

                  

                   apex_mail.push_queue();

                end;

                 

                i am receiving an email

                • 5. Re: Email Through Oracle Apex 18.1
                  cormaco

                  utl_smtp works because it is not dependend on apex email settings.

                   

                  Did you read the link I posted about configuring apex email?

                  • 6. Re: Email Through Oracle Apex 18.1
                    Omar Baig

                    No Cormaco

                    • 7. Re: Email Through Oracle Apex 18.1
                      Omar Baig

                      Dear Cormaco,

                      I go through your providing document but still i am getting the same error ora-24247.

                      • 8. Re: Email Through Oracle Apex 18.1
                        Billy~Verreynne

                        Then 1 of 2 potential issues:

                         

                        The IP address used for the UTL_SMTP test, and accepted by the ACL, is not used by APEX's mail. (No ACL exist)

                         

                        APEX does not have the required privs to use the ACL, used by the UTL_SMTP test. (ACL access failed)

                        • 9. Re: Email Through Oracle Apex 18.1
                          Mike Kutz

                          Omar Baig wrote:

                           

                           

                           

                          ORA-29278: SMTP transient error: 421 Service not available

                           

                          421 is an SMTP error.  Contact your postmaster (ie email server administrator) to find out why the connection is being refused.

                           

                          It could be because the email server thinks the database is a spammer.

                           

                          My $0.02

                           

                          MK

                          • 10. Re: Email Through Oracle Apex 18.1
                            Omar Baig

                            Please find the below mention images, I have the utl_smtp on same HOST that is why it is not reflecting in the Table

                             

                             

                            • 11. Re: Email Through Oracle Apex 18.1
                              Omar Baig

                              Assalamoalikum,

                              By the Grace of Allah Pak, I have resolved the issue while running the below scripts while connecting from SYS User

                               

                              BEGIN

                               

                                DBMS_NETWORK_ACL_ADMIN.create_acl (

                                  acl          => 'power_users_apex.xml',

                                  description  => 'access to apex email',

                                  principal    => 'APEX_180100',

                                  is_grant     => TRUE,

                                  privilege    => 'connect',

                                  start_date   => SYSTIMESTAMP,

                                  end_date     => NULL);

                               

                                COMMIT;

                               

                              END;

                               

                              BEGIN

                               

                                DBMS_NETWORK_ACL_ADMIN.add_privilege (

                                  acl          => 'power_users_apex.xml',

                                  principal    => 'APEX_180100',

                                  is_grant     => TRUE,

                                  privilege    => 'resolve');

                               

                                COMMIT;

                               

                              END;

                               

                               

                              BEGIN

                               

                                DBMS_NETWORK_ACL_ADMIN.assign_acl (

                                  acl => 'power_users_apex.xml',

                                  host => '10.1.1.2',

                                  lower_port => 25,

                                  upper_port => NULL);

                                  commit;

                               

                              END;

                               

                              furthermore

                               

                              login Oracle Apex with admin privileges (Workspace: INTERNAL, User:youruser, password:yourpass)

                              go to Manage Instance \ Instance Settings, set my SMTP Host Address and resolved the issues. For further clarification follow my video link http://youtu.be/NCxMOPzUjBg

                              • 12. Re: Email Through Oracle Apex 18.1
                                Omar Baig

                                Kindly remember I am using Oracle Apex (18.1).

                                • 13. Re: Email Through Oracle Apex 18.1
                                  fac586

                                  Omar Baig wrote:

                                   

                                  By the Grace of Allah Pak, I have resolved the issue while running the below scripts

                                  Please do not make religious statements in the forum. They are irrelevant in this domain and likely to cause offence to other users who do not share your beliefs.

                                  • 14. Re: Email Through Oracle Apex 18.1
                                    Omar Baig

                                    Dear All,

                                    Assalamoalikum, According to me the solution which I have mention above thread is correct, Kindly suggest me what should I do? Leave it as "Not Answered" or mark it as "Correct Answer"?

                                     

                                     

                                    Regards,

                                    Omar Baig