10 Replies Latest reply on Mar 27, 2018 3:05 PM by anand_gp

    apex_mail.send failing with ORA-29278

    anand_gp

      Hello,

       

      I was testing to send mail from my development system and it is failing.

      I use apex 4.2.6 EPG on Windows 7 O/S.

      From "SQL workshop" -> "sql command"  I executed below command -

       

      DECLARE

      l_body CLOB;

      BEGIN

      l_body := 'Thank you for your interest in the APEX_MAIL package.' || utl_tcp.crlf || utl_tcp.crlf;

      l_body := l_body ||' Sincerely,' || utl_tcp.crlf;

      l_body := l_body ||' The Application Express Dev Team' || utl_tcp.crlf;

      apex_mail.send(

      p_to => 'mymailid@gmail.com',

      p_from => 'mymailid@gmail.com',

      p_body => l_body,

      p_subj => 'APEX_MAIL Package - Plain Text message');

      END;

      /

       

      If I check apex_mail_queue

      SELECT * FROM APEX_MAIL_QUEUE
      

       

      I see queued up mail with below error -

       

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

       

      Can someone help me to solve this issue.  Thanks.

       

      On database I see below -

       

      SQL> sho parameter smtp

       

       

      NAME                                 TYPE        VALUE

      ------------------------------------ ----------- ---------------------------

      smtp_out_server                      string

      SQL>

       

      Regards,

      -Anand

        • 2. Re: apex_mail.send failing with ORA-29278
          anand_gp

          Hello Alli,

           

          Thanks for the input, but that did not help me.

          The thread tells that SMTP server may not be up and running.  And I also suspect the same (not sure).  And there is no clarity on how to check this in Windows machine.  The thread says -

           

          Go to Control Panel->Add or Remove Programs->Click on

          Add/Remove Wndows Components

          Check IIS check box.

          Select Internet Information Service (IIS) option and click on Details button

          Check whether SMTP Service is checked or not.

          If not selected then select SMTP check box.

           

          However, if I click on Add/Remove component, I don't see SMTP service under IIS (after expanding all child options) -

           

           

          Few more details (incase if it helps in finding the issue), if I query my database -

           

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

           

           

          HOST             LOWER_PORT UPPER_PORT PRIVILE STATUS

          ---------------- ---------- ---------- ------- -------

          *                connect GRANTED

          *                resolve GRANTED

           

           

           

           

           

          SQL> SELECT acl, principal, privilege, is_grant, start_date, end_date FROM dba_network_acl_privileges;

           

           

          ACL                       PRINCIPAL  PRIVILE IS_GRANT      START_DATE    END_DATE

          ------------------------- -------------------------------------------------------

          /sys/acls/power_user      APEX_040200  connect true

           

          Regards,

          -Anand

          • 3. Re: apex_mail.send failing with ORA-29278
            Billy~Verreynne

            You need a SMTP server - that needs not only to be running, but CORRECTLY configured. Alternatively, you need to use an existing working SMTP server.

             

            A s/w installation or feature checkbox is not going to ensure a working SMTP server. The SMTP server needs to resolve a domain's MX records, disallow open relays, etc. It is not a trivial matter of ticking a SMTP service checkbox, as you seems to think it is.

             

            By the way, Microsoft Window's SMTP server is part of Exchange.

            • 4. Re: apex_mail.send failing with ORA-29278
              anand_gp

              Hello Billy,

               

              That sounds like, sending email from APEX is not an easy job ???

              APEX document doesn't talk about need of SMTP Server on APEX server !!

              I thought, just configuring email in APEX will make me send emails, but it is not the case

              It feels like only SMTP expert (or Network Admin) only can setup SMTP server ?

              Please advice.

               

              Thanks,

              -Anand

              • 5. Re: apex_mail.send failing with ORA-29278
                Billy~Verreynne

                You seems confused.

                 

                APEX is  not a mail server. Oracle database is not a mail server. Apache Tomcat is not a mail server.

                 

                A mail server.. is a mail server. Always have been.

                 

                SQL is the language a client communicates with a database. HTTP is the language a client communicates with a web server. SMTP is the language a client communicates with a mail server.

                 

                Oracle database talks SMTP via PL/SQL package UTL_SMTP. APEX uses this to provide a high level send mail application interface.

                 

                Thus Oracle/APEX can do the client walk and talk using SMTP.

                 

                Now you are blaming the client for not also providing the mail server? That is like blaming TOAD or SQL-Developer client not coming with a ready to use Oracle database. Or blaming Oracle Linux operating system not coming with default hardware to run it on.

                 

                Oracle/APEX needs to use a mail server to send mail. This server must support SMTP as application language. Very few developers actually installs a brand new SMTP server to use. The vast majority use their existing corporate mail server, or use their ISP's mail server. And this typically only needs the developer to tell Oracle/APEX the network address (IP or hostname) of the mail server, and which port to use (default 25 for SMTP).

                • 6. Re: apex_mail.send failing with ORA-29278
                  anand_gp

                  Hello Billy,

                   

                  Thanks for the clarification.  I was confused because of lack of knowledge in that area.  All I wanted to know was, to send mail from my apex application.  Now it's clear that, APEX need a mail server to send the mail. 

                  Can u please clarify me few more points.  I use corporate mail with below SMTP detail.  Can I use that as a medium to send mail?  Does that need any permission from corporate mail department?  My other question is, can I use my gmail a/c details to achieve this?  Or any other way other than me buying SMTP server?  Thanks.

                   

                   

                  Regards,

                  -Anand

                  • 7. Re: apex_mail.send failing with ORA-29278
                    Billy~Verreynne

                    The SMTP server and protocol is used to send e-mails. The POP and IMAP protocols are used to receive mail.

                     

                    You cannot use POP or IMAP for sending e-mail.

                     

                    SMTP, POP and IMAP are clear text application protocols. This means if this TCP traffic (the wire protocol used for running the application protocol over) is inspected/sniffed, the data send and data received are easily readable by that 3rd party. This compromises security (logon credentials) and confidentiality (e-mails can be read, attachments intercepted, etc).

                     

                    So instead of using plain TCP, TLS is used as encrypted protocol - running the application protocol over encrypted TLS.

                     

                    UTL_SMTP (Oracle database's SMTP interface) supports TLS - I have never used APEX mail, but I assume newer versions also supports TLS. Suggest you google search configuring a TLS SMTP server for use with APEX.

                     

                    Yes, you should use your corporate SMTP server. Yes, you can also use Gmail's SMTP server I believe, but TLS is needed, and valid Gmail credentials. Using Gmail however require your database server platform to have Internet access. This raises security issues that needs to be addressed.

                     

                    Permission to use the corporate SMTP server - it depends on your company's security and mail policies. Usually though the SMTP server is not heavily firewalled as clients need to communicate with it. The SMTP server may however expect your username and password to be supplied for a successful connection.

                    • 8. Re: apex_mail.send failing with ORA-29278
                      anand_gp

                      When you say -

                      Using Gmail however require your database server platform to have Internet access. This raises security issues that needs to be addressed.

                      Do you mean, internet access to my system where I have installed apex and DB?  Since it's a standalone development machine I already use internet connection in that machine.  By enabling (or configuring) gmail SMTP in that machine causes security issue you mean?  Please clarify.  Thanks.

                      • 9. Re: apex_mail.send failing with ORA-29278
                        Billy~Verreynne

                        If the database server is your local machine, and it is connected to the Internet (directly and not via a web proxy only), then yes - you should be able to use the Gmail SMTP server.

                         

                        In this case your local machine already has a security concern as it (and now your local Oracle database), is exposed to the Internet. You should be running a restrictive local firewall on your machine, and expose minimal (preferably no) service endpoints to the Internet. The o/s also should be hardened.

                        • 10. Re: apex_mail.send failing with ORA-29278
                          anand_gp

                          Thanks!

                          Appreciate if you can give any links which explains how to use gmail SMTP to configure apex mail.  Thanks.  Meanwhile I will also check in parallel.