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

    error sending mail in the database

    599921
      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
        • 1. Re: error sending mail in the database
          CraigB
          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...