ORA-29278 When Trying to Use SMTP with GMail

Hi all,
Some time ago I got reached by a client with this error to send SMTP emails using GMail service with a wallet.
This is a generic error, in general the string identified the error returned from email service layer.

# Generic:

ORA-29278: SMTP transient error: string

# Error I was facing:

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

Basically it was related to some intermittence on GMail’s service and got back to normal after a while. This is also well documented in this post by Hâvard Kristiansen.
However, I couldn’t use scripts there because of my wallet. Then I found the very useful commands below, that is what I want to share with you:

# Checking for SSL Certificate:

[oracle@grepora-srvr]$ openssl s_client -crlf -quiet -connect smtp.gmail.com:587 -starttls smtp
depth=3 C = US, O = Equifax, OU = Equifax Secure Certificate Authority
verify return:1
depth=2 C = US, O = GeoTrust Inc., CN = GeoTrust Global CA
verify return:1
depth=1 C = US, O = Google Inc, CN = Google Internet Authority G2
verify return:1
depth=0 C = US, ST = California, L = Mountain View, O = Google Inc, CN = smtp.gmail.com
verify return:1
250 SMTPUTF8
^C

(return:1 means "passed".)

# Testing SMTP on Database (code):

DECLARE
  k_host            CONSTANT VARCHAR2(100) := 'smtp.gmail.com';
  k_port            CONSTANT INTEGER       := 587;
  k_wallet_path     CONSTANT VARCHAR2(100) := 'file:/u01/app/oracle/admin/grepora/wallet/email';
  k_wallet_password CONSTANT VARCHAR2(100) := '****';
  k_domain          CONSTANT VARCHAR2(100) := 'grepora.com'; 
  k_username        CONSTANT VARCHAR2(100) := 'suporte@grepora.com';
  k_password        CONSTANT VARCHAR2(100) := 'mysecurepass';
  k_sender          CONSTANT VARCHAR2(100) := 'suporte@grepora.com';
  k_recipient       CONSTANT VARCHAR2(100) := 'suporte@grepora.com';
  k_subject         CONSTANT VARCHAR2(100) := 'Test TLS mail';
  k_body            CONSTANT VARCHAR2(100) := 'Message body';
  l_conn    utl_smtp.connection;
  l_reply   utl_smtp.reply;
  l_replies utl_smtp.replies;
  BEGIN
    dbms_output.put_line('utl_smtp.open_connection');

    l_reply := utl_smtp.open_connection
               ( host                          => k_host
               , port                          => k_port
               , c                             => l_conn
               , wallet_path                   => k_wallet_path
               , wallet_password               => k_wallet_password
               , secure_connection_before_smtp => FALSE
               );

    IF l_reply.code != 220
    THEN
      raise_application_error(-20000, 'utl_smtp.open_connection: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.ehlo');

    l_replies := utl_smtp.ehlo(l_conn, k_domain);

    FOR ri IN 1..l_replies.COUNT
    LOOP
      dbms_output.put_line(l_replies(ri).code||' - '||l_replies(ri).text);
    END LOOP;

    dbms_output.put_line('utl_smtp.starttls');

    l_reply := utl_smtp.starttls(l_conn);

    IF l_reply.code != 220
    THEN
      raise_application_error(-20000, 'utl_smtp.starttls: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.ehlo');

    l_replies := utl_smtp.ehlo(l_conn, k_domain);

    FOR ri IN 1..l_replies.COUNT
    LOOP
      dbms_output.put_line(l_replies(ri).code||' - '||l_replies(ri).text);
    END LOOP;

    dbms_output.put_line('utl_smtp.auth');

    l_reply := utl_smtp.auth(l_conn, k_username, k_password, 'PLAIN');

    IF l_reply.code != 235
    THEN
      raise_application_error(-20000, 'utl_smtp.auth: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.mail');

    l_reply := utl_smtp.mail(l_conn, k_sender);

    IF l_reply.code != 250
    THEN
      raise_application_error(-20000, 'utl_smtp.mail: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.rcpt');

    l_reply := utl_smtp.rcpt(l_conn, k_recipient);

    IF l_reply.code NOT IN (250, 251)
    THEN
      raise_application_error(-20000, 'utl_smtp.rcpt: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.open_data');

    l_reply := utl_smtp.open_data(l_conn);

    IF l_reply.code != 354
    THEN
      raise_application_error(-20000, 'utl_smtp.open_data: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.write_data');

    utl_smtp.write_data(l_conn, 'From: '||k_sender||utl_tcp.crlf);
    utl_smtp.write_data(l_conn, 'To: '||k_recipient||utl_tcp.crlf);
    utl_smtp.write_data(l_conn, 'Subject: '||k_subject||utl_tcp.crlf);
    utl_smtp.write_data(l_conn, utl_tcp.crlf||k_body);

    dbms_output.put_line('utl_smtp.close_data');

    l_reply := utl_smtp.close_data(l_conn);

    IF l_reply.code != 250
    THEN
      raise_application_error(-20000, 'utl_smtp.close_data: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.quit');

    l_reply := utl_smtp.quit(l_conn);

    IF l_reply.code != 221
    THEN
      raise_application_error(-20000, 'utl_smtp.quit: '||l_reply.code||' - '||l_reply.text);
    END IF;

  EXCEPTION
    WHEN    utl_smtp.transient_error
         OR utl_smtp.permanent_error
    THEN
      BEGIN
        utl_smtp.quit(l_conn);
      EXCEPTION
        WHEN    utl_smtp.transient_error
             OR utl_smtp.permanent_error
        THEN
          NULL;
      END;

      raise_application_error(-20000, 'Failed to send mail due to the following error: '||SQLERRM);

  END;
  /

Hope it helps you!