Configure Oracle Network ACL for Gmail SMTP

Introduction

Oracle Network Access Control List (ACL) controls which database users are allowed to make network connections from the Oracle Database to external hosts. In other words, it governs the outbound traffic leaving the database toward the outside world. It is commonly required when database PL/SQL packages such as UTL_SMTP, UTL_HTTP, and UTL_TCP need to access external network services.

In this article, we will configure an Oracle Network ACL on Oracle Database 19c and use it to send an email through Gmail SMTP using UTL_SMTP. Gmail SMTP is used here purely to demonstrate the configuration end to end; the same steps apply to any SMTP server, so feel free to substitute your own SMTP host, port, and credentials. We will also configure an Oracle Wallet for TLS and use a Gmail App Password for authentication.

The complete configuration will be demonstrated step by step, starting with the error received when no ACL is configured and ending with a successful email delivery.

Network ACL vs Wallet ACL

ACL	Purpose
Network ACL	Controls access from the database to external hosts and ports.
Wallet ACL	Controls which database users can access credentials stored in an Oracle Wallet.

Why Network ACL is Required?

Oracle restricts network access from database PL/SQL by default. This prevents a database user from directly connecting to arbitrary external systems.

For example, when APPUSER tries to connect to Gmail SMTP without the required ACL, Oracle returns ORA-24247: network access denied by access control list (ACL).

Real-Life Use Cases

• Sending email using UTL_SMTP.
• Calling REST APIs using UTL_HTTP.
• Connecting to external services using UTL_TCP.
• Sending database alerts through an external SMTP server.
• Integrating Oracle Database with external applications and services.

Prerequisites

• Network connectivity from the database server to the SMTP server.
• Gmail account with 2-Step Verification enabled.
• Gmail App Password for SMTP authentication.

Environment Used in This Guide

Hostname	orares.oraeasy.com
OS	OL 7.9
Database Version	19c (19.31)
PDB	ORCLPDB
Database User	APPUSER
SMTP Server	smtp.gmail.com
SMTP Port	587
Wallet Path	/u01/app/oracle/wallets/gmail

Parameters Used in UTL_SMTP Configuration

Parameter	Description
l_conn	SMTP connection object used to establish and maintain the SMTP connection.
host	Specify the hostname of the SMTP server.
port	Specify the SMTP port used for the connection.
wallet_path	Specify the Oracle Wallet path containing the trusted certificates required for TLS.
EHLO domain	Specify the hostname or domain name of the database server that identifies the SMTP client.
secure_host	Specify the SMTP server hostname whose certificate should be validated during TLS.
l_reply	Variable used to store the SMTP reply returned by the authentication operation.
l_username	Specify the Gmail email address used for SMTP authentication.
l_app_password	Specify the Google App Password generated for the Gmail account. Do not use the normal Gmail password.
UTL_SMTP.ALL_SCHEMES	Allows UTL_SMTP to use the supported SMTP authentication schemes.

Summary of Activities

This article walks through the following, in order:

1. Seeing the impact of not using Network ACL.
2. Precheck of network connectivity and configuring Wallet & Gmail certificates.
3. Configuring ACL at the database level.
4. Testing connection, STARTTLS and sending mail.
5. Sending mail with attachment.

Seeing the Impact of Not Using Network ACL

Step 1: Try to connect Gmail SMTP

Before configuring the Network ACL, let's try to connect to Gmail SMTP using UTL_SMTP. This will demonstrate the error that occurs when the database user does not have network access.

SQL> show user
USER is "APPUSER"
SQL>

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
    l_conn UTL_SMTP.connection;
BEGIN

    DBMS_OUTPUT.PUT_LINE(
        'Connecting to smtp.gmail.com:587...'
    );

    l_conn := UTL_SMTP.OPEN_CONNECTION(
                host => 'smtp.gmail.com',
                port => 587
              );

    DBMS_OUTPUT.PUT_LINE(
        'SMTP connection successful.'
    );

    UTL_SMTP.QUIT(l_conn);

END;
/  
Connecting to smtp.gmail.com:587...
DECLARE
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 19
ORA-06512: at "SYS.UTL_TCP", line 295
ORA-06512: at "SYS.UTL_SMTP", line 164
ORA-06512: at "SYS.UTL_SMTP", line 201
ORA-06512: at line 9

The connection fails with ORA-24247 because the database user does not yet have permission to access the external SMTP host.

Precheck of network connectivity and configuring Wallet & Gmail certificates

Step 1: Check the connectivity to Gmail SMTP server

Before creating the wallet, confirm the database server can actually resolve and reach the SMTP host on the required port. This rules out a basic network or firewall issue before touching any Oracle configuration.

[oracle@orares ~]$ getent hosts smtp.gmail.com
2404:6800:4013:813::6c smtp.gmail.com

[oracle@orares ~]$ nc -vz smtp.gmail.com 587
Ncat: Version 7.50 ( https://nmap.org/ncat )
Ncat: Connected to 192.178.158.108:587.
Ncat: 0 bytes sent, 0 bytes received in 0.04 seconds.
[oracle@orares ~]$

Step 2: Create the Wallet Directory

Create a dedicated directory to hold the Oracle Wallet, restricted to the oracle OS user only.

[oracle@orares ~]$ mkdir -p /u01/app/oracle/wallets/gmail
[oracle@orares ~]$ chmod 700 /u01/app/oracle/wallets/gmail

Step 3: Create the Auto Login Wallet

This creates the actual Oracle Wallet that will hold the trusted CA certificates used for TLS validation against Gmail. An auto-login wallet (created with -auto_login) lets the database open it automatically at runtime without prompting for the wallet password, which is exactly what UTL_SMTP needs when it opens a TLS connection from PL/SQL — there's no interactive prompt available inside a database session. The password entered here only protects the wallet at the OS level; it isn't referenced anywhere in the PL/SQL code, since wallet_path alone is enough for an auto-login wallet.

orapki is an Oracle utility used to create and manage Oracle Wallets and certificates.

[oracle@orares ~]$ echo $ORACLE_HOME
/u01/app/oracle/19c/db_1
[oracle@orares ~]$
[oracle@orares ~]$ echo $PATH
/u01/app/oracle/19c/db_1/bin:/usr/bin/java/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:.
[oracle@orares ~]$
[oracle@orares ~]$  orapki wallet create  -wallet /u01/app/oracle/wallets/gmail  -auto_login
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2025, Oracle and/or its affiliates. All rights reserved.

Enter password:
Enter password again:
Operation is successfully completed.
[oracle@orares ~]$

Step 4: Verify the Wallet

Confirm the wallet was created successfully before importing any certificates into it.

[oracle@orares ~]$ orapki wallet display -wallet /u01/app/oracle/wallets/gmail
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2025, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Trusted Certificates:

Step 5: Obtain Gmail Certificate Chain

The Gmail SMTP certificate chain can be obtained using OpenSSL from the database server.

[oracle@orares ~]$ openssl s_client -connect smtp.gmail.com:587 -starttls smtp -showcerts  /tmp/gmail_cert_chain.txt 2>&1
[oracle@orares ~]$
[oracle@orares ~]$ ls -lrth /tmp/gmail_cert_chain.txt
-rw-r--r--. 1 oracle oinstall 8.1K Sep  4 18:29 /tmp/gmail_cert_chain.txt
[oracle@orares ~]$

Step 6: Verify the Certificate Chain

[oracle@orares ~]$ grep -n "BEGIN CERTIFICATE" /tmp/gmail_cert_chain.txt
14:-----BEGIN CERTIFICATE-----
41:-----BEGIN CERTIFICATE-----
72:-----BEGIN CERTIFICATE-----
[oracle@orares ~]$

The output should show the certificates returned by the Gmail SMTP server.

Step 7: Extract the Required CA Certificates

In this lab, the Gmail certificate chain contains the Gmail server certificate followed by the Google Trust Services certificates.

[oracle@orares ~]$ awk '/-----BEGIN CERTIFICATE-----/{n++} n==1{print} /-----END CERTIFICATE-----/{if(n==1) exit}' /tmp/gmail_cert_chain.txt > /tmp/gmail_server.pem
[oracle@orares ~]$
[oracle@orares ~]$ ls -lrth /tmp/gmail_server.pem
[oracle@orares ~]$ awk '/-----BEGIN CERTIFICATE-----/{n++} n==2{print} /-----END CERTIFICATE-----/{if(n==2) exit}' /tmp/gmail_cert_chain.txt > /tmp/WR2.pem
[oracle@orares ~]$
[oracle@orares ~]$ ls -lrth /tmp/WR2.pem
-rw-r--r--. 1 oracle oinstall 1.8K Sep  4 18:31 /tmp/WR2.pem
[oracle@orares ~]$
[oracle@orares ~]$ awk '/-----BEGIN CERTIFICATE-----/{n++} n==3{print} /-----END CERTIFICATE-----/{if(n==3) exit}'  /tmp/gmail_cert_chain.txt > /tmp/GTS_Root_R1.pem
[oracle@orares ~]$
[oracle@orares ~]$ ls -lrth /tmp/GTS_Root_R1.pem
-rw-r--r--. 1 oracle oinstall 1.9K Sep  4 18:32 /tmp/GTS_Root_R1.pem
[oracle@orares ~]$

Step 8: Verify the Certificates

[oracle@orares ~]$ openssl x509 -in /tmp/gmail_server.pem -noout -subject -issuer
subject= /CN=smtp.gmail.com
issuer= /C=US/O=Google Trust Services/CN=WR2
[oracle@orares ~]$
[oracle@orares ~]$ openssl x509 -in /tmp/WR2.pem -noout -subject -issuer
subject= /C=US/O=Google Trust Services/CN=WR2
issuer= /C=US/O=Google Trust Services LLC/CN=GTS Root R1
[oracle@orares ~]$
[oracle@orares ~]$ openssl x509 -in /tmp/GTS_Root_R1.pem -noout -subject -issuer
subject= /C=US/O=Google Trust Services LLC/CN=GTS Root R1
issuer= /C=BE/O=GlobalSign nv-sa/OU=Root CA/CN=GlobalSign Root CA
[oracle@orares ~]$

Step 9: Import the Trusted Certificates into the Wallet

With the two CA certificates extracted in Step 7 (WR2.pem and GTS_Root_R1.pem), import each one into the wallet as a trusted certificate using orapki wallet add -trusted_cert. Both are needed because they form the chain: WR2 is the intermediate CA that actually signed Gmail's server certificate, and GTS_Root_R1 is the root CA that signed WR2 in turn. Oracle needs the full chain present in the wallet to validate Gmail's certificate during the TLS handshake in UTL_SMTP.STARTTLS. Since this is an auto-login wallet, each orapki command still prompts once for the wallet password to authorize the modification, even though no password is needed to read from the wallet at runtime.

[oracle@orares ~]$ orapki wallet add -wallet /u01/app/oracle/wallets/gmail -trusted_cert -cert /tmp/WR2.pem
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2025, Oracle and/or its affiliates. All rights reserved.

Cannot modify auto-login (sso) wallet
Enter wallet password:
Operation is successfully completed.
[oracle@orares ~]$
[oracle@orares ~]$ orapki wallet add -wallet /u01/app/oracle/wallets/gmail -trusted_cert -cert /tmp/GTS_Root_R1.pem
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2025, Oracle and/or its affiliates. All rights reserved.

Cannot modify auto-login (sso) wallet
Enter wallet password:
Operation is successfully completed.
[oracle@orares ~]$

The Gmail server certificate itself is not imported as a trusted certificate. The trusted CA certificates are sufficient for certificate chain validation.

Step 10: Verify the Wallet Certificates

[oracle@orares ~]$  orapki wallet display -wallet /u01/app/oracle/wallets/gmail
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2025, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        CN=GTS Root R1,O=Google Trust Services LLC,C=US
Subject:        CN=WR2,O=Google Trust Services,C=US
[oracle@orares ~]$
[oracle@orares ~]$ ls -l /u01/app/oracle/wallets/gmail
total 8
-rw-------. 1 oracle oinstall 3131 Sep  4 18:34 cwallet.sso
-rw-------. 1 oracle oinstall    0 Sep  4 18:18 cwallet.sso.lck
-rw-------. 1 oracle oinstall 3086 Sep  4 18:34 ewallet.p12
-rw-------. 1 oracle oinstall    0 Sep  4 18:18 ewallet.p12.lck
[oracle@orares ~]$

Both the WR2 intermediate and the GTS Root R1 certificate now appear as trusted certificates in the wallet, which is exactly what TLS validation will check against when Oracle connects to Gmail.

Configuring ACL at the database level

Before using the Gmail App Password for authentication, the database user must be allowed to access passwords stored in the Oracle Wallet.

Step 1: Grant UTL_SMTP Privilege

Grant the required EXECUTE privilege on UTL_SMTP to the application user.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO
SQL> alter session set container=ORCLPDB;

Session altered.

SQL> GRANT EXECUTE ON UTL_SMTP TO APPUSER;

Grant succeeded.

Step 2: Check Existing Wallet ACL

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ORCLPDB                        READ WRITE NO

SQL> SELECT wallet_path,
       principal,
       principal_type,
       privilege,
       grant_type
FROM dba_wallet_aces
WHERE principal = 'APPUSER';

no rows selected

If no rows are returned, the user does not currently have the required Wallet ACL privilege.

Step 3: Configure Wallet ACL

The USE_PASSWORDS privilege allows APPUSER to use passwords stored in the specified Oracle Wallet.

SQL> BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE(
    wallet_path => 'file:/u01/app/oracle/wallets/gmail',
    ace         => XS$ACE_TYPE(
                      privilege_list => XS$NAME_LIST('use_passwords'),
                      principal_name => 'APPUSER',
                      principal_type => XS_ACL.PTYPE_DB
                    )
  );
END;
/  

PL/SQL procedure successfully completed.

Step 3: Verify Wallet ACL

SQL> SELECT wallet_path,
       principal,
       principal_type,
       privilege,
       grant_type
FROM dba_wallet_aces
WHERE principal = 'APPUSER';

WALLET_PATH                              PRINCIPAL            PRINCIPAL_T PRIVILEGE                      GRANT
---------------------------------------- -------------------- ----------- ------------------------------ -----
file:/u01/app/oracle/wallets/gmail       APPUSER              DATABASE    USE_PASSWORDS                  GRANT

SQL>

Expected result should show APPUSER with the USE_PASSWORDS privilege.

Step 4: Configure Network ACL for Gmail SMTP

Now we will allow APPUSER to connect to Gmail SMTP on port 587.

SQL> BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host       => 'smtp.gmail.com',
    lower_port => 587,
    upper_port => 587,
    ace        => XS$ACE_TYPE(
                    privilege_list => XS$NAME_LIST('smtp'),
                    principal_name => 'APPUSER',
                    principal_type => XS_ACL.PTYPE_DB
                  )
  );
END;
/

PL/SQL procedure successfully completed.

The SMTP privilege allows APPUSER to establish SMTP connections to smtp.gmail.com on port 587.

Step 5: Verify Network ACL

SQL> SELECT host,
       lower_port,
       upper_port,
       principal,
       principal_type,
       privilege,
       grant_type
FROM dba_host_aces
WHERE LOWER(host) = 'smtp.gmail.com';

HOST                      LOWER_PORT UPPER_PORT PRINCIPAL            PRINCIPAL_T PRIVILEGE                      GRANT
------------------------- ---------- ---------- -------------------- ----------- ------------------------------ -----
smtp.gmail.com                   587        587 APPUSER              DATABASE    SMTP                           GRANT

The output should show APPUSER with the SMTP privilege for port 587.

Testing connection, STARTTLS and sending mail

Step 1: Test SMTP Connection.

Test the SMTP connection which failed earlier with ORA-24247.

SQL> show user
USER is "APPUSER"
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
    l_conn UTL_SMTP.connection;
BEGIN

    DBMS_OUTPUT.PUT_LINE(
        'Connecting to smtp.gmail.com:587...'
    );

    l_conn := UTL_SMTP.OPEN_CONNECTION(
                host => 'smtp.gmail.com',
                port => 587
              );

    DBMS_OUTPUT.PUT_LINE(
        'SMTP connection successful.'
    );

    UTL_SMTP.QUIT(l_conn);

  END;
/

Connecting to smtp.gmail.com:587...
SMTP connection successful.

PL/SQL procedure successfully completed.

Unlike the very first attempt at the top of this article, the connection now succeeds, confirming the Network ACL is correctly in place.

Step 2: Test STARTTLS

The Network ACL is now configured. Before testing Gmail authentication, let's verify that Oracle can establish a secure STARTTLS connection with Gmail.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
    l_conn UTL_SMTP.connection;
BEGIN
    l_conn := UTL_SMTP.OPEN_CONNECTION(
                host        => 'smtp.gmail.com',
                port        => 587,
                wallet_path => 'file:/u01/app/oracle/wallets/gmail'
              );

    UTL_SMTP.EHLO(l_conn, 'orares.oraeasy.com');

    UTL_SMTP.STARTTLS(l_conn, 'smtp.gmail.com');

    UTL_SMTP.EHLO(l_conn, 'orares.oraeasy.com');

    DBMS_OUTPUT.PUT_LINE('STARTTLS negotiation successful.');

    UTL_SMTP.QUIT(l_conn);
END;
/  

STARTTLS negotiation successful.

PL/SQL procedure successfully completed.

This confirms that the Network ACL, Wallet ACL, Oracle Wallet, certificates, and TLS configuration are working correctly.

Step 3: Test Gmail Authentication

Gmail does not allow the normal account password to be used for this SMTP authentication method. A Google App Password should be generated and used instead. Check this to see how to generate Google App Password.

The generated App Password should be used in the l_app_password variable instead of the normal Gmail password.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
    l_conn         UTL_SMTP.connection;
    l_reply        UTL_SMTP.reply;
    l_username     VARCHAR2(320) := 'oraeasyy@gmail.com';
    l_app_password VARCHAR2(100) := 'Your@Password';
BEGIN
    DBMS_OUTPUT.PUT_LINE('Opening Gmail SMTP connection...');

    l_conn := UTL_SMTP.OPEN_CONNECTION(
                host        => 'smtp.gmail.com',
                port        => 587,
                wallet_path => 'file:/u01/app/oracle/wallets/gmail'
              );

    UTL_SMTP.EHLO(l_conn, 'orares.oraeasy.com');

    DBMS_OUTPUT.PUT_LINE('Starting TLS...');
    UTL_SMTP.STARTTLS(l_conn, 'smtp.gmail.com');

    UTL_SMTP.EHLO(l_conn, 'orares.oraeasy.com');

    DBMS_OUTPUT.PUT_LINE('Authenticating with Gmail...');

    l_reply := UTL_SMTP.AUTH(
                 l_conn,
                 l_username,
                 l_app_password,
                 UTL_SMTP.ALL_SCHEMES
               );

    DBMS_OUTPUT.PUT_LINE('Gmail authentication successful.');

    UTL_SMTP.QUIT(l_conn);
END;
/ 
Opening Gmail SMTP connection...
Starting TLS...
Authenticating with Gmail...
Gmail authentication successful.

PL/SQL procedure successfully completed

Step 4: Send Test Email

Now that the SMTP connection, TLS and authentication are working, we can send a test email using UTL_SMTP.

For Sender & Recipient, we are using the same mail id..

SET SERVEROUTPUT ON
SQL> DECLARE
    l_conn         UTL_SMTP.connection;
    l_username     VARCHAR2(320) := 'oraeasyy@gmail.com';
    l_app_password VARCHAR2(100) := 'Your@Password';
    l_sender       VARCHAR2(320) := 'oraeasyy@gmail.com';
    l_recipient    VARCHAR2(320) := 'oraeasyy@gmail.com';
BEGIN
    DBMS_OUTPUT.PUT_LINE('Opening Gmail SMTP connection...');

    l_conn := UTL_SMTP.OPEN_CONNECTION(
                host        => 'smtp.gmail.com',
                port        => 587,
                wallet_path => 'file:/u01/app/oracle/wallets/gmail'
              );

    UTL_SMTP.EHLO(l_conn, 'orares.oraeasy.com');

    DBMS_OUTPUT.PUT_LINE('Starting TLS...');
    UTL_SMTP.STARTTLS(l_conn, 'smtp.gmail.com');

    UTL_SMTP.EHLO(l_conn, 'orares.oraeasy.com');

    DBMS_OUTPUT.PUT_LINE('Authenticating...');
    UTL_SMTP.AUTH(
        l_conn,
        l_username,
        l_app_password,
        UTL_SMTP.ALL_SCHEMES
    );

    DBMS_OUTPUT.PUT_LINE('Sending email...');

    UTL_SMTP.MAIL(l_conn, l_sender);
    UTL_SMTP.RCPT(l_conn, l_recipient);

    UTL_SMTP.OPEN_DATA(l_conn);

    UTL_SMTP.WRITE_DATA(
        l_conn,
        'From: ' || l_sender || UTL_TCP.CRLF ||
        'To: ' || l_recipient || UTL_TCP.CRLF ||
        'Subject: Oracle 19c Gmail SMTP Test' || UTL_TCP.CRLF ||
        UTL_TCP.CRLF ||'Test email sent successfully from Oracle Database 19c using UTL_SMTP.'
    );

    UTL_SMTP.CLOSE_DATA(l_conn);
    UTL_SMTP.QUIT(l_conn);

    DBMS_OUTPUT.PUT_LINE('Email sent successfully.');

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);

        BEGIN
            UTL_SMTP.QUIT(l_conn);
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;

        RAISE;
END;
/  

Opening Gmail SMTP connection...
Starting TLS...
Authenticating...
Sending email...
Email sent successfully.

PL/SQL procedure successfully completed.

SQL>

The test email should now be delivered to the configured recipient.

Send Email with File Attachment

The same UTL_SMTP connection can also be used to send file attachments. The file is read from the database server using UTL_FILE, encoded using Base64, and added to the email using MIME multipart formatting.

Step 1: Create an Oracle DIRECTORY

UTL_FILE needs an Oracle DIRECTORY object pointing at the OS location of the file to attach, along with read privilege granted to the application user.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ORCLPDB                        READ WRITE NO
SQL>
SQL> show user
USER is "SYS"
SQL>
SQL> CREATE OR REPLACE DIRECTORY MAIL_ATTACH_DIR
     AS '/u01/app/oracle'; 

Directory created.

SQL> GRANT READ ON DIRECTORY MAIL_ATTACH_DIR TO APPUSER;

Grant succeeded.

SQL>

Step 2: Place the file

Place the file to be attached in the corresponding OS directory on the database server. Also provide ncessary permission to file.

[oracle@orares ~]$ cd /u01/app/oracle
[oracle@orares oracle]$
[oracle@orares oracle]$ ls test_acl.txt
test_acl.txt
[oracle@orares oracle]$
[oracle@orares oracle]$ chmod 755 test_acl.txt

[oracle@orares oracle]$ ll test_acl.txt
-rwxr-xr-x. 1 oracle oinstall 71 Sep 12 20:19 test_acl.txt

The file needs to be readable by the oracle OS user, since that's the account the database process runs as when UTL_FILE opens it.

Step 3: Send Email with Attachment

For a file attachment, the email must be created as a MIME multipart message and the file content must be Base64 encoded before transmission.

SQL> show user
USER is "APPUSER"
SQL> SET SERVEROUTPUT ON
SQL>
DECLARE
    l_conn         UTL_SMTP.connection;
    l_username     VARCHAR2(320) := 'oraeasyy@gmail.com';
    l_app_password VARCHAR2(100) := 'Your@Password';
    l_sender       VARCHAR2(320) := 'oraeasyy@gmail.com';
    l_recipient    VARCHAR2(320) := 'oraeasyy@gmail.com';

    l_file         UTL_FILE.file_type;
    l_buffer       RAW(57);
    l_filename     VARCHAR2(255) := 'test_acl.txt';
    l_boundary     VARCHAR2(100) := '----=_Boundary_Oracle_12345';

BEGIN

    DBMS_OUTPUT.PUT_LINE('Opening Gmail SMTP connection...');

    l_conn := UTL_SMTP.OPEN_CONNECTION(
                host        => 'smtp.gmail.com',
                port        => 587,
                wallet_path => 'file:/u01/app/oracle/wallets/gmail'
              );

    UTL_SMTP.EHLO(l_conn, 'orares.oraeasy.com');

    DBMS_OUTPUT.PUT_LINE('Starting TLS...');

    UTL_SMTP.STARTTLS(l_conn, 'smtp.gmail.com');

    UTL_SMTP.EHLO(l_conn, 'orares.oraeasy.com');

    DBMS_OUTPUT.PUT_LINE('Authenticating...');

    UTL_SMTP.AUTH(
        l_conn,
        l_username,
        l_app_password,
        UTL_SMTP.ALL_SCHEMES
    );

    DBMS_OUTPUT.PUT_LINE('Sending email...');

    UTL_SMTP.MAIL(l_conn, l_sender);
    UTL_SMTP.RCPT(l_conn, l_recipient);

    UTL_SMTP.OPEN_DATA(l_conn);

    UTL_SMTP.WRITE_DATA(
        l_conn,
        'From: ' || l_sender || UTL_TCP.CRLF ||
        'To: ' || l_recipient || UTL_TCP.CRLF ||
        'Subject: Oracle 19c File Attachment Test' || UTL_TCP.CRLF ||
        'MIME-Version: 1.0' || UTL_TCP.CRLF ||
        'Content-Type: multipart/mixed; boundary="' ||
        l_boundary || '"' || UTL_TCP.CRLF ||
        UTL_TCP.CRLF
    );

    UTL_SMTP.WRITE_DATA(
        l_conn,
        '--' || l_boundary || UTL_TCP.CRLF ||
        'Content-Type: text/plain; charset="UTF-8"' || UTL_TCP.CRLF ||
        UTL_TCP.CRLF ||
        'Hello,' || UTL_TCP.CRLF ||
        UTL_TCP.CRLF ||
        'Please find the attached file.' || UTL_TCP.CRLF ||
        'Email sent from Oracle Database 19c.' || UTL_TCP.CRLF ||
        UTL_TCP.CRLF
    );

    UTL_SMTP.WRITE_DATA(
        l_conn,
        '--' || l_boundary || UTL_TCP.CRLF ||
        'Content-Type: application/octet-stream; name="' ||
        l_filename || '"' || UTL_TCP.CRLF ||
        'Content-Disposition: attachment; filename="' ||
        l_filename || '"' || UTL_TCP.CRLF ||
        'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF ||
        UTL_TCP.CRLF
    );

    l_file := UTL_FILE.FOPEN(
                'MAIL_ATTACH_DIR',
                l_filename,
                'rb',
                32767
              );

    LOOP
        BEGIN
            UTL_FILE.GET_RAW(l_file, l_buffer, 57);

            UTL_SMTP.WRITE_DATA(
                l_conn,
                UTL_RAW.CAST_TO_VARCHAR2(
                    UTL_ENCODE.BASE64_ENCODE(l_buffer)
                ) || UTL_TCP.CRLF
            );

        EXCEPTION
            -- FIXED: Changed from NO_DATA_NEEDED to handle EOF safely
            WHEN NO_DATA_FOUND THEN
                EXIT;

            WHEN UTL_FILE.INVALID_OPERATION THEN
                EXIT;
        END;
    END LOOP;

    UTL_FILE.FCLOSE(l_file);

    UTL_SMTP.WRITE_DATA(
        l_conn,
        '--' || l_boundary || '--' || UTL_TCP.CRLF
    );

    UTL_SMTP.CLOSE_DATA(l_conn);

    UTL_SMTP.QUIT(l_conn);

    DBMS_OUTPUT.PUT_LINE('Email with attachment sent successfully.');

EXCEPTION
    WHEN OTHERS THEN

        IF UTL_FILE.IS_OPEN(l_file) THEN
            UTL_FILE.FCLOSE(l_file);
        END IF;

        DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);

        BEGIN
            UTL_SMTP.QUIT(l_conn);
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;

        RAISE;
END;
/


Opening Gmail SMTP connection...
Starting TLS...
Authenticating...
Sending email...
Email with attachment sent successfully.

PL/SQL procedure successfully completed.

SQL>

Generate Gmail App Password

1. Enable 2-Step Verification on your Google Account. An App Password can only be created after 2-Step Verification is enabled.
2. Open the Google App Passwords page and sign in to your Google Account.
3. Enter an appropriate name, such as Oracle SMTP, and click Create. Google will generate a 16-digit App Password. Copy this password and use it as the SMTP password in your Oracle configuration.

Note: Store the App Password securely. Do not share or publish it. If you change your Google Account password, existing App Passwords are revoked and a new one must be created.

Common Errors and Troubleshooting

Error	Possible Cause
ORA-24247	Required Network ACL is missing.
ORA-29024	Certificate validation failed or required CA certificate is missing from the wallet.
ORA-28759	Oracle could not access the wallet.
ORA-29273	Network operation failed. Check ACL, wallet, DNS and network connectivity.
ORA-29279	Gmail SMTP authentication failed. Verify the Gmail account and App Password.

Security Considerations

• Grant Network ACL access only to the required database users.
• Restrict the ACL to the required hostname and port whenever possible.
• Protect the Oracle Wallet directory using appropriate OS permissions.
• Do not store the Gmail App Password directly in production PL/SQL source code.
• Use a dedicated email account for database-generated notifications where appropriate.
• Review Network ACL and Wallet ACL privileges periodically.

Thank you for reading!

I hope this content has been helpful to you. Your feedback and suggestions are always welcome — feel free to leave a comment or reach out with any queries.

Abhishek Shrivastava

📧 Email: oraeasyy@gmail.com
🌐 Website: www.oraeasy.com

Follow Us