ORA-29279: SMTP Permanent Error - Fix Oracle Email Sending

ORA-29279: SMTP Permanent Error

Error Overview

Error Text: ORA-29279: SMTP permanent error: NNN <message_from_smtp_server>

ORA-29279 is raised by the UTL_SMTP package when the remote SMTP server returns a permanent error response code (5xx). The three-digit SMTP response code and the server message are included in the error text. Common examples:

• ORA-29279: SMTP permanent error: 550 5.1.1 User unknown
• ORA-29279: SMTP permanent error: 553 5.1.3 Invalid address
• ORA-29279: SMTP permanent error: 554 5.7.1 Relay access denied

The “permanent” classification means the SMTP server actively rejected the message and retrying without a configuration change will produce the same result. This is distinct from ORA-29278 (SMTP transient error — 4xx codes), which indicates a temporary failure worth retrying.

Common Causes

1. Relay Access Denied (550 or 554 Relay Error)

• The Oracle database server’s IP is not authorized to relay mail through the SMTP server
• Corporate SMTP relay server requires authentication or IP whitelisting
• SMTP server is configured as closed relay and rejects unauthenticated senders

2. Invalid Recipient or Sender Address

• Recipient email address does not exist or is formatted incorrectly
• Sender address (FROM) uses a domain the SMTP server does not accept
• RCPT TO or MAIL FROM contains malformed address syntax

3. ACL (Access Control List) Not Configured in Oracle

• Oracle 11g+: The database server process does not have network access to the SMTP server
• DBMS_NETWORK_ACL_ADMIN ACL not created for the SMTP hostname and port
• ACL exists but the executing user is not granted the required privilege

4. Authentication Required by the SMTP Server

• SMTP server requires AUTH LOGIN, AUTH PLAIN, or SASL authentication
• UTL_SMTP is being used without sending AUTH commands
• Credentials are incorrect or expired

5. Message Size or Content Policy Rejection

• Email attachment or body size exceeds the SMTP server’s configured limit
• Content scanning blocked the message (spam filter, antivirus)
• HTML content or specific headers triggered a policy rejection

Diagnostic Queries

Check Oracle Network ACLs for SMTP Access

-- 12c and later: Check network ACLs (DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES):
SELECT
  acl,
  host,
  lower_port,
  upper_port
FROM dba_network_acls
WHERE host LIKE '%smtp%'
   OR lower_port IN (25, 465, 587)
   OR upper_port IN (25, 465, 587)
ORDER BY host;

-- Which users have CONNECT privilege through any ACL:
SELECT
  acl,
  host,
  principal,
  privilege,
  is_grant,
  lower_port,
  upper_port
FROM dba_network_acl_privileges
WHERE privilege = 'connect'
ORDER BY host, principal;

-- Check if a specific user can connect to an SMTP server:
SELECT
  DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(
    aclid    => (SELECT aclid FROM dba_network_acls
                 WHERE host = 'mail.example.com' AND lower_port = 25),
    user     => 'SCHEMA_OWNER',
    privilege => 'connect'
  ) AS has_privilege
FROM dual;

Check ACLs in Oracle 12c+ Using the Unified Model

-- In 12c+, ACLs are managed differently:
SELECT
  host,
  lower_port,
  upper_port,
  aclid
FROM dba_host_acls
ORDER BY host;

-- Check privilege for a specific user and host:
SELECT
  DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(
    host      => 'mail.example.com',
    lower_port => 25,
    upper_port => 25,
    user      => 'SCHEMA_OWNER',
    privilege  => 'connect'
  ) AS has_privilege
FROM dual;

Identify UTL_SMTP Usage in the Codebase

-- Find all packages and procedures using UTL_SMTP:
SELECT DISTINCT
  owner,
  name,
  type
FROM dba_source
WHERE UPPER(text) LIKE '%UTL_SMTP%'
ORDER BY owner, type, name;

-- Find all uses of UTL_MAIL (higher-level wrapper):
SELECT DISTINCT owner, name, type
FROM dba_source
WHERE UPPER(text) LIKE '%UTL_MAIL%'
ORDER BY owner, type, name;

-- Check UTL_MAIL configuration:
SELECT name, value FROM v$parameter WHERE name = 'smtp_out_server';

Test Connectivity to SMTP Server

-- Test whether Oracle can reach the SMTP server at all:
-- (Run as a privileged user with ACL connect rights)
DECLARE
  c UTL_SMTP.CONNECTION;
BEGIN
  c := UTL_SMTP.OPEN_CONNECTION('mail.example.com', 25, 15);
  DBMS_OUTPUT.PUT_LINE('Connected. Server greeting: ');
  UTL_SMTP.QUIT(c);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Connection failed: ' || SQLERRM);
END;
/

Step-by-Step Resolution

1. Identify the SMTP Error Code and Message

The ORA-29279 text includes the SMTP response code and message:

SMTP Code	Meaning	Action
550 5.1.1	User unknown	Check recipient address
554 5.7.1	Relay access denied	Whitelist DB server IP on SMTP relay
553 5.1.3	Bad sender address	Fix MAIL FROM address
535 5.7.8	Auth credentials invalid	Fix username/password
552 5.3.4	Message size exceeds limit	Reduce email size

2. Create or Fix the Oracle ACL for SMTP Access (11g+)

-- Grant connect access to the SMTP server for a specific user:
-- (Must run as SYS or user with DBA privilege)

-- 11g syntax:
BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl         => 'smtp_access.xml',
    description => 'SMTP server access',
    principal   => 'SCHEMA_OWNER',
    is_grant    => TRUE,
    privilege   => 'connect'
  );
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl  => 'smtp_access.xml',
    host => 'mail.example.com',
    lower_port => 25,
    upper_port => 25
  );
  COMMIT;
END;
/

-- 12c+ syntax (preferred):
BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host        => 'mail.example.com',
    lower_port  => 25,
    upper_port  => 25,
    ace         => xs$ace_type(
                     privilege_list => xs$name_list('connect'),
                     principal_name => 'SCHEMA_OWNER',
                     principal_type => xs_acl.ptype_db
                   )
  );
  COMMIT;
END;
/

3. Fix “Relay Access Denied” (554 5.7.1)

Ask your SMTP administrator to whitelist the Oracle database server’s outbound IP address on the SMTP relay. In the meantime, use an SMTP server that allows relay from the database server:

-- Change the SMTP server to one that allows relay from this DB:
-- Update the UTL_MAIL SMTP_OUT_SERVER parameter:
ALTER SYSTEM SET smtp_out_server = 'internal-relay.example.com:25' SCOPE=BOTH;

-- Or in your UTL_SMTP code, switch to an authorized relay:
DECLARE
  c UTL_SMTP.CONNECTION;
BEGIN
  c := UTL_SMTP.OPEN_CONNECTION('authorized-relay.example.com', 25);
  -- ... rest of SMTP dialog
END;
/

4. Add SMTP Authentication (When Server Requires AUTH)

CREATE OR REPLACE PROCEDURE send_email_auth(
  p_to      IN VARCHAR2,
  p_subject IN VARCHAR2,
  p_body    IN VARCHAR2
) AS
  c           UTL_SMTP.CONNECTION;
  v_username  VARCHAR2(100) := '[email protected]';
  v_password  VARCHAR2(100) := 'smtp_password';
  v_auth_str  VARCHAR2(500);
BEGIN
  c := UTL_SMTP.OPEN_CONNECTION('smtp.example.com', 587);
  UTL_SMTP.EHLO(c, 'oracledb.example.com');

  -- Send AUTH LOGIN:
  UTL_SMTP.COMMAND(c, 'AUTH LOGIN');
  -- Base64 encode username and password:
  v_auth_str := UTL_RAW.CAST_TO_VARCHAR2(
    UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(v_username))
  );
  UTL_SMTP.COMMAND(c, v_auth_str);
  v_auth_str := UTL_RAW.CAST_TO_VARCHAR2(
    UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(v_password))
  );
  UTL_SMTP.COMMAND(c, v_auth_str);

  -- Send the message:
  UTL_SMTP.MAIL(c, '[email protected]');
  UTL_SMTP.RCPT(c, p_to);
  UTL_SMTP.OPEN_DATA(c);
  UTL_SMTP.WRITE_DATA(c, 'From: Oracle DB <[email protected]>' || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(c, 'To: ' || p_to || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(c, 'Subject: ' || p_subject || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(c, p_body);
  UTL_SMTP.CLOSE_DATA(c);
  UTL_SMTP.QUIT(c);

EXCEPTION
  WHEN UTL_SMTP.TRANSIENT_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('Transient SMTP error: ' || SQLERRM);
    UTL_SMTP.QUIT(c);
    RAISE;
  WHEN UTL_SMTP.PERMANENT_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('Permanent SMTP error: ' || SQLERRM);
    UTL_SMTP.QUIT(c);
    RAISE;
END;
/

5. Fix Invalid Address (550 5.1.1 or 553 5.1.3)

-- Validate email address format before sending:
CREATE OR REPLACE FUNCTION is_valid_email(p_email IN VARCHAR2) RETURN BOOLEAN IS
BEGIN
  RETURN REGEXP_LIKE(
    p_email,
    '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
  );
END;
/

-- In the sending procedure, validate before calling UTL_SMTP:
IF NOT is_valid_email(p_to) THEN
  RAISE_APPLICATION_ERROR(-20001, 'Invalid recipient email: ' || p_to);
END IF;

6. Use UTL_MAIL for Simpler Email Sending

-- UTL_MAIL wraps UTL_SMTP with a simpler interface:
-- First install UTL_MAIL if not present:
-- @?/rdbms/admin/utlmail.sql
-- @?/rdbms/admin/prvtmail.plb

-- Set SMTP server:
ALTER SYSTEM SET smtp_out_server = 'mail.example.com:25' SCOPE=BOTH;

-- Grant execute:
GRANT EXECUTE ON UTL_MAIL TO schema_owner;

-- Send email:
BEGIN
  UTL_MAIL.SEND(
    sender      => '[email protected]',
    recipients  => '[email protected]',
    subject     => 'Test from Oracle',
    message     => 'This is a test email from Oracle UTL_MAIL.'
  );
END;
/

Prevention Strategies

1. Test Email Configuration After Every Environment Change

-- Run a test email after deploying email-sending code:
BEGIN
  UTL_MAIL.SEND(
    sender     => '[email protected]',
    recipients => '[email protected]',
    subject    => 'Oracle Email Test - ' || SYS_CONTEXT('USERENV', 'DB_NAME'),
    message    => 'Email configuration test at ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
  );
  DBMS_OUTPUT.PUT_LINE('Email sent successfully.');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Email test failed: ' || SQLERRM);
END;
/

2. Document SMTP Server and Authentication Requirements

• Record the SMTP host, port, authentication method, and credentials in a secure vault
• Include SMTP relay IP whitelist requirements in the database provisioning checklist

3. Implement Robust Error Handling in Email Procedures

-- Always handle both transient and permanent errors:
EXCEPTION
  WHEN UTL_SMTP.PERMANENT_ERROR THEN
    INSERT INTO email_error_log (error_time, error_code, error_msg, recipient, subject)
    VALUES (SYSTIMESTAMP, SQLCODE, SQLERRM, p_to, p_subject);
    COMMIT;
    -- Do not re-raise permanent errors unless the calling process must fail
  WHEN UTL_SMTP.TRANSIENT_ERROR THEN
    -- Schedule retry
    INSERT INTO email_retry_queue (send_time, recipient, subject, body, retry_count)
    VALUES (SYSTIMESTAMP + INTERVAL '5' MINUTE, p_to, p_subject, p_body, 0);
    COMMIT;

Related Errors

• ORA-29278 - SMTP transient error (4xx codes — retry-able)
• ORA-24247 - Network access denied by ACL
• ORA-06512 - At line (PL/SQL stack trace)

Emergency Response

Quick ACL Grant to Unblock Email

-- As SYS — grant connect access immediately:
BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host       => 'mail.example.com',
    lower_port => 25,
    upper_port => 25,
    ace        => xs$ace_type(
                    privilege_list => xs$name_list('connect'),
                    principal_name => 'SCHEMA_OWNER',
                    principal_type => xs_acl.ptype_db
                  )
  );
  COMMIT;
END;
/

Quick Relay Fix

-- Switch to a relay that allows Oracle database server IP:
ALTER SYSTEM SET smtp_out_server = 'open-relay.internal.example.com:25' SCOPE=BOTH;

Post-Fix Validation

-- Confirm email works end-to-end:
BEGIN
  UTL_MAIL.SEND(
    sender => '[email protected]',
    recipients => '[email protected]',
    subject => 'Fix confirmed',
    message => 'ORA-29279 resolved at ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
  );
END;
/