14 Replies Latest reply: Jul 15, 2009 1:23 PM by SanjayRs RSS

    configure SMTP protocol in Oracle Database

    674304
      HI

      What should I do to configure Database to send email using utl_mail package

      note : i use 3g to connect to internet

      Thanks in advance
        • 1. Re: configure SMTP protocol in Oracle Database
          Sentinel
          you need to tell Oracle where to find your SMTP server:

          See this Re: utl_mail.send problem where damorgan provides the needed command.
          • 2. Re: configure SMTP protocol in Oracle Database
            674304
            Thanks for ur replay

            First i run the following script utlmail.sql,prvtmail.plb
            Second ALTER SYSTEM SET smtp_out_server='smtp.127.0.0.1.com' SCOPE=SPFILE;
            after that i shutdown the database and i startup it
            after that i execute the following pl/sql block

            BEGIN
            UTL_MAIL.send(sender => 'me@mydomain.com',
            recipients => 'gonasser@hotmail.com',
            subject => 'Testing ..',
            message => 'Test Message!');
            END;
            /

            the following errors had rised :

            ORA-29278: SMTP transient error: 421 Service not available
            ORA-06512: at "SYS.UTL_SMTP", line 21
            ORA-06512: at "SYS.UTL_SMTP", line 97
            ORA-06512: at "SYS.UTL_SMTP", line 139
            ORA-06512: at "SYS.UTL_MAIL", line 405
            ORA-06512: at "SYS.UTL_MAIL", line 594
            ORA-06512: at line 2

            what is the problem ?
            • 3. Re: configure SMTP protocol in Oracle Database
              SanjayRs
              Are you able to send email from your localhost ?

              Is your localhost a smtp server. ?

              SS
              • 4. Re: configure SMTP protocol in Oracle Database
                674304
                i don't know :(

                what i should do to send email?

                can i use localhost to do it ?

                i user 3g to connect to internet, can i use it to make the service available ?
                • 5. Re: configure SMTP protocol in Oracle Database
                  SanjayRs
                  You can Install smtp server or configure localhost as your smtp server.

                  [Free Smtp Server|http://www.softstack.com/freesmtp.html]

                  Then configure as [my  Post|http://forums.oracle.com/forums/message.jspa?messageID=3598528#3599385] Shows

                  If your company has a smtp server you should configure to point at smtp.yourcompany.com

                  SS
                  • 6. Re: configure SMTP protocol in Oracle Database
                    674304
                    Thanks a lot

                    I have Application Server, I use it for Application Express

                    can i use it as smtp server ?
                    • 7. Re: configure SMTP protocol in Oracle Database
                      SanjayRs
                      excalibur wrote:
                      Thanks a lot

                      I have Application Server, I use it for Application Express

                      can i use it as smtp server ?
                      The example I posted was for windows, you need to configure your localhost for Unix or Windows.

                      I am not aware what Application Server you are running, can you send email from any other utility.

                      What OS are you running, if you have Windows Server can configure it as smtp server.

                      SS
                      • 8. Re: configure SMTP protocol in Oracle Database
                        674304
                        i'm using Windows XP Professional edition

                        i have Oracle Application Server 10g
                        • 9. Re: configure SMTP protocol in Oracle Database
                          SanjayRs
                          [Smtp Server on Windows XP |http://www.ehow.com/how_4489548_set-up-smtp-server-windows.html]

                          SS
                          • 10. Re: configure SMTP protocol in Oracle Database
                            674304
                            could i use google SMTP server to send email with utl_mail,
                            if i can, how can i do it?

                            gmail SMTP Server :

                            SMTP Server: smtp.gmail.com
                            Port: 587
                            Username and password: some gmail account

                            Edited by: excalibur on Jul 15, 2009 6:27 AM

                            Edited by: excalibur on Jul 15, 2009 6:28 AM
                            • 11. Re: configure SMTP protocol in Oracle Database
                              674304
                              Help plz
                              • 12. Re: configure SMTP protocol in Oracle Database
                                SanjayRs
                                excalibur wrote:
                                could i use google SMTP server to send email with utl_mail,
                                if i can, how can i do it?

                                gmail SMTP Server :

                                SMTP Server: smtp.gmail.com
                                Port: 587
                                Username and password: some gmail account
                                Google's SMTP server requires additional security information and is thus incompatible with Oracle's UTL_MAIL package.

                                SS
                                • 13. Re: configure SMTP protocol in Oracle Database
                                  Sentinel
                                  You have a problem here. See the definition that you created for the SMTP_OUT_SERVER. You prepended smtp. and appended .com onto what appears to me to be a static IP address. Strip off the stmp. and .com so you have the following:
                                  ALTER SYSTEM SET smtp_out_server='127.0.0.1' SCOPE=SPFILE;
                                  which by the way is the loopback address and probably could be replaced with the value 'localhost' instead.

                                  Once you get the smtp_out_server string correct things should start working for you.
                                  excalibur wrote:
                                  Thanks for ur replay

                                  First i run the following script utlmail.sql,prvtmail.plb
                                  Second ALTER SYSTEM SET smtp_out_server=<font color='red'>'smtp.127.0.0.1.com'</font> SCOPE=SPFILE;
                                  after that i shutdown the database and i startup it
                                  after that i execute the following pl/sql block

                                  BEGIN
                                  UTL_MAIL.send(sender => 'me@mydomain.com',
                                  recipients => 'gonasser@hotmail.com',
                                  subject => 'Testing ..',
                                  message => 'Test Message!');
                                  END;
                                  /

                                  the following errors had rised :

                                  ORA-29278: SMTP transient error: 421 Service not available
                                  ORA-06512: at "SYS.UTL_SMTP", line 21
                                  ORA-06512: at "SYS.UTL_SMTP", line 97
                                  ORA-06512: at "SYS.UTL_SMTP", line 139
                                  ORA-06512: at "SYS.UTL_MAIL", line 405
                                  ORA-06512: at "SYS.UTL_MAIL", line 594
                                  ORA-06512: at line 2

                                  what is the problem ?
                                  • 14. Re: configure SMTP protocol in Oracle Database
                                    SanjayRs
                                    Try this and post the error you get.

                                    Note Substitute your email address in the Recipient
                                    DECLARE
                                      v_From      VARCHAR2(80) := 'me@mydomain.com';
                                      v_Recipient VARCHAR2(80) := 'fromme@dot.com';
                                      v_Subject   VARCHAR2(80) := 'Sendmail test';
                                      v_Mail_Host VARCHAR2(30) := 'localhost';
                                      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);
                                    END;
                                    /
                                    SS