7 Replies Latest reply on Sep 8, 2011 4:01 PM by Billy~Verreynne

    Need help in setting SMTP server for sending mail ???

    AnkitV
      Hi
      I have windows XP ver 2.18 and Oracle Database 10g Enterprise Edition Release 10.2.0.1.0.
      I need to send mail from pl/sql using UTL_MAIL.
      I came to know from reading that we need to set our machine as SMTP server first. So i did it via IIS in control panel.
      Under Default SMTP Virtual server , i made a domain using alias option ( LOCAL domain), and tried setting it in Oracle via following command :

      ALTER SYSTEM SET smtp_out_server='ANKIT-VOHRA.na.nisource.net' SCOPE=SPFILE;
      SQL> shutdown immediate;
      SQL> startup
      SQL> show parameter smtp_out_server

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      smtp_out_server string ANKIT-VOHRA.na.nisource.net
      SQL>

      Now i tried sending mail :
      BEGIN
      UTL_MAIL.send(sender => 'me@nisource.net',
      recipients => 'ankit.vohra1@gmail.com',
      --cc         => 'person3@domain.com',
      --bcc        => 'myboss@domain.com',
      subject => 'UTL_MAIL Test',
      message => 'If you get this message it worked!');
      END;

      I got following error :

      Error report:
      ORA-29279: SMTP permanent error: 550 5.7.1 Unable to relay for ankit.vohra1@gmail.com
      ORA-06512: at "SYS.UTL_SMTP", line 21
      ORA-06512: at "SYS.UTL_SMTP", line 99
      ORA-06512: at "SYS.UTL_SMTP", line 241
      ORA-06512: at "SYS.UTL_MAIL", line 424
      ORA-06512: at "SYS.UTL_MAIL", line 594
      ORA-06512: at line 2
      29279. 00000 - "SMTP permanent error: %s"
      *Cause:    A SMTP permanent error occurred.
      *Action:   Correct the error and retry the SMTP operation.

      I think that I did not set the server properly.
      Can u please help me in setting the SMTP server properly and successfully sending mail !!
        • 1. Re: Need help in setting SMTP server for sending mail ???
          Kim Berg Hansen
          No problems in Oracle - sorry I cannot help you :-)

          Your STMP server in Windows is giving you the error, that it cannot relay (that is it cannot take the mail fra Oracle and relay/forward it to another SMTP server.)
          You have to find a setting in your Windows STMP server to allow it to relay.

          If you cannot find it - perhaps there is a forum on Microsoft site to help with SMTP setup?

          Edited by: Kim Berg Hansen on Sep 7, 2011 2:44 PM
          • 2. Re: Need help in setting SMTP server for sending mail ???
            AnkitV
            Hi

            I did some setting of relay restrictions option in IIS default SMTP server properties. So my code did not give any error.

            BEGIN
            UTL_MAIL.send(sender => 'ankit_203@yahoo.com',
            recipients => 'ankit.vohra1@gmail.com',
            --cc         => 'person3@domain.com',
            --bcc        => 'myboss@domain.com',
            subject => 'UTL_MAIL Test',
            message => 'If you get this message it worked!');
            exception
            when others then
            dbms_output.put_line('Error: '||SQLERRM);
            END;

            anonymous block completed

            But the mail is not received !!!
            Can anyone help me in understanding/resolving this.
            • 3. Re: Need help in setting SMTP server for sending mail ???
              JustinCave
              OK, let's take a step back...

              Why are you setting up your own SMTP server? Are you able to send email using your nisource.net account from the machine where Oracle is installed? If so, is your email client configured to use an SMTP server? You should be using that server.

              Unless you own the nisource.net domain, it sounds like you're trying to set up your own open relay. That's going to cause virtually every mail server on the planet to reject your emails because they'll appear to be spam and/or have forged headers.

              Justin
              • 4. Re: Need help in setting SMTP server for sending mail ???
                AnkitV
                Hi Justin..

                In this case, the database is installed on my PC only (and i am just trying for last many days to send a mail using my pl/sql code) and i used the default domain name present in IIS for smtp_server_out parameter. Frankly, i don't know how should i setup SMTP server for sending mail OR which server should be used.
                From the things that i came across on the internet, i thought specifying the domain name mentioned in my IIS will do. But i don't think its correct.

                Please suggest how can i make it work.. How should i do setup and which domain name should i use...

                Thanks a lot ..
                • 5. Re: Need help in setting SMTP server for sending mail ???
                  Billy~Verreynne
                  user572632 wrote:

                  Please suggest how can i make it work.. How should i do setup and which domain name should i use...
                  As mentioned, this is not an Oracle problem at all. Oracle acts as an e-mail send client. It uses the SMTP application protocol to send mail - as would other mail clients use.

                  The error it receives is from the mail server. There's nothing Oracle itself can do regarding the error - like pulling out a lead pipe and start beating the mail server into submission.

                  So do not let the Oracle side confuse you or the problem you are experiencing.

                  Test your e-mail connectivity on the platform running the Oracle server, manually.

                  Do so using the telnet command and the SMTP protocol.

                  Talking SMTP is easy. Like many other application protocols it is a clear text protocol. Easy to understand. Easy to use.

                  Simple example:
                  // telnet into the mail server on port 25
                  /home/billy> telnet mail-server 25
                  Trying mail-server...
                  Connected to mail-server
                  Escape character is '^]'.
                  220 mail-server.some-domain.com SMTP Service-All transactions Logged. Contact postmaster for issues Thu, 8 Sep 2011 19:15:55 
                  // use the HELO command to identify yourself using either your hostname or IP address
                  HELO 10.251.93.30
                  250 mail-server.some-domain.com Hello [10.251.93.30]
                  // start a mail send by telling the mail server the recipient
                  MAIL FROM:<me@some-domain.com>
                  250 2.1.0 me@some-domain.com....Sender OK
                  // tell the mail server the recipient's address
                  RCPT TO:<someone.else>@some-domain.com>
                  250 2.1.5 someone.else>@some-domain.com
                  // send a Mime Body as a the e-mail - simplest is a plain text
                  // mail containing a single header line for the subject, followed
                  // by the e-mail text. Terminate mail body with a single period.
                  DATA
                  354 Start mail input; end with <CRLF>.<CRLF>
                  Subject: Test E-Mail
                  
                  Testing 1,2 and 3...
                  .
                  250 2.6.0 <XS22JcXu980000b92b@mail-server.some-domain.com> Queued mail for delivery
                  // end conversation with mail server
                  QUIT
                  221 2.0.0 mail-server.some-domain.com Service closing transmission channel
                  Connection closed by foreign host.
                  If this does now work, if the e-mail submitted does not arrive at the recipient.. then nor will e-mail work via Oracle.

                  So get the above to work first. And only then try it in Oracle.
                  • 6. Re: Need help in setting SMTP server for sending mail ???
                    AnkitV
                    i got ur point and i am ok with it..
                    my problem is the mail-server.. !!!
                    how do i know one to use ??
                    OR do i need to setup one ??
                    • 7. Re: Need help in setting SMTP server for sending mail ???
                      Billy~Verreynne
                      user572632 wrote:
                      i got ur point and i am ok with it..
                      my problem is the mail-server.. !!!
                      how do i know one to use ??
                      OR do i need to setup one ??
                      In the corporate world you will use the local LAN smtp server - as this will usually be able to relay your e-mail to the outside world.

                      In the home/PC environment, you need to use the same mail server that your e-mail reader is using - I assume your database is on the same platform than your mail reader?

                      The only issue is that this smtp mail server will be hosted/owned by your ISP (Internet Service Provider). They may use authentication on that smtp server. Which means the mail client needs to supply a username and password in order to send e-mail.

                      Oracle's UTL_SMTP and UTL_MAIL packages (for 10g) do not support SMTP authentication. And if I'm not mistaken, so too for Oracle 11g.

                      In which case you need to manually use the smtp server in your PL/SQL code (and not UTL_MAIL) in order to first authenticate with the mail server.

                      Using the telnet test as I've described above, will also show whether authentication is needed or not.

                      Also keep in mind that 11g has a lot more security restrictions (a good thing) - and you need to create a ACL (Access Control List) to allow PL/SQL code to communicate over tcp with another server, such as a mail server.