1 Reply Latest reply on Mar 26, 2008 1:50 PM by CraigB

    error sending mail in the database

    599921
    599921 Mar 26, 2008 11:41 AM
      Hi ,
      I wrote one procedure to send a mail from the database.But I am getting error.Please observe the code and let me give the solution for this.Here I found am not giving service name.But I don't knoe the service name. Then how to send the mail..
      SQL>  DECLARE
  2     c UTL_SMTP.CONNECTION;
  3    
  4     PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
  5     BEGIN
  6       UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
  7     END;
  8    
  9   BEGIN
 10     c := UTL_SMTP.OPEN_CONNECTION('smtp-server.acme.com');
 11     UTL_SMTP.HELO(c,'gmail.com');
 12  UTL_SMTP.MAIL(c, 'abc@gmail.com');
 13  UTL_SMTP.RCPT(c, 'def@gmail.com');
 14  send_header('From',    '"Sender" <abc@gmail.com>');
 15   send_header('To',      '"Recipient" <def@accenture.com>');
 16      UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!');
 17      UTL_SMTP.CLOSE_DATA(c);
 18      UTL_SMTP.QUIT(c);
 19    EXCEPTION
 20      WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
 21        BEGIN
 22          UTL_SMTP.QUIT(c);
 23        EXCEPTION
 24          WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
 25            NULL; -- When the SMTP server is down or unavailable, we don't have
 26                  -- a connection to the server. The QUIT call will raise an
 27                  -- exception that we can ignore.
 28        END;
 29        raise_application_error(-20000,
 30          'Failed to send mail due to the following error: ' || sqlerrm);
 31    END;
 32  /
 DECLARE
*
ERROR at line 1:
ORA-20000: Failed to send mail due to the following error: ORA-29278: SMTP transient error: 421
Service not available
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at line 29
      Thank you

      Message was edited by:
      User71408
      • 444 Views
      • Tags:
        • 1. Re: error sending mail in the database
          CraigB
          CraigB Mar 26, 2008 1:50 PM (in response to 599921)
          In order to send an email using UTL_SMTP, you MUST have a valid SMTP Host . Typically, the SMTP host is your company email server. However, if you are not on a network, you should be able to use a public Email server so long as the public email server allows remote connections. There is a thread that discusses using "gmail.com" as the SMTP host, but the author of the thread was never able to make it work. I have used my College email server successfully but I had to log into the email server using my user account and password. If you can use an email client, such as Outlook Express, to send and receive email, you should be able to use UTL_SMTP to send and receive email from that email server.

          Hope this helps.
          Craig...