A practical pattern for Developers, DBAs and those enjoying the technical journey from Oracle to PostgreSQL
Migrations from Oracle to PostgreSQL get sold on licensing savings, open-source flexibility, and ecosystem momentum. Those benefits are real, and we are happy to show you how real they are. What's less often discussed — until you're three weeks into cutover preparation and someone runs a search across the codebase — is the long tail of Oracle-supplied packages that have no direct PostgreSQL equivalent.
If you've worked with Oracle for any length of time, you've almost certainly seen UTL_MAIL.SEND() called from a trigger, a job, or a PL/SQL procedure to fire off a notification when something interesting happens in the database. It's convenient, it is part of the database, and it works. PostgreSQL has no built-in equivalent. There is no 'pg_mail', no contrib module that ships with the core distribution, and no out-of-the-box way for a function or trigger to push a message onto an SMTP server.
This isn't a bug in PostgreSQL — it's a deliberate philosophical difference. PostgreSQL takes the view that the database shouldn't be making outbound network calls during a transaction. Oracle takes the opposite view and bundles the capability in. Both positions have their merits even though I have been told in no uncertain terms that a database should be a database, not a mail server ... Neither helps you on Monday morning when you've got 400 PL/SQL procedures referencing UTL_MAIL.SEND and a go-live date.
This post walks through a pattern we've used at Pebble IT to close that gap cleanly, without forcing a rewrite of every caller.
The goal is straightforward: any existing PL/SQL that calls UTL_MAIL.SEND(sender, recipients, cc, bcc, subject, message, mime_type, priority) should be able to call a PostgreSQL function with the same name and the same signature — and an email should arrive. Callers shouldn't need to know that the underlying mechanics have changed.
The reality, of course, is that they have changed, and it's worth being honest with the business about what's different before we start.
Delivery is now asynchronous. Oracle's UTL_MAIL.SEND is synchronous — it talks to the SMTP server during the call, and if delivery fails, the caller sees an exception they can handle in PL/SQL. The pattern we're about to build is fire-and-forget from the caller's perspective: the function returns as soon as the message is queued, and actual SMTP delivery happens out-of-process. For 95% of use cases this is an improvement (faster transactions, no SMTP outage stalling your database), but if you have PL/SQL that depends on catching mail-send exceptions inline, you'll need to handle that separately.
pg_notify is best-effort, not guaranteed. PostgreSQL's LISTEN/NOTIFY mechanism is the trigger we'll use to wake up our mail-sending process. But if the listener happens to be down at the moment a notification fires, that notification is lost. The good news: the message itself isn't lost, because we'll be persisting it to a queue table first. The listener simply needs to check the queue table for unprocessed rows on startup. We'll cover that in the Python listener step.
We're going to build five components:
The result is that existing PL/SQL — once translated to PL/pgSQL — can keep calling utl_mail.send(...) exactly as it did before. The mechanics underneath are different and arguably better, but the caller doesn't need to care.
Let's walk through each item.
A dedicated schema keeps the new objects clearly separated from your application data and makes it obvious to anyone reading the database what these objects are doing there. It also makes permissions easier to manage — you can grant EXECUTE on the schema's functions without having to manage individual object grants.
CREATE SCHEMA IF NOT EXISTS utl_mail;
The queue table is the heart of the pattern. Every call to utl_mail.send produces exactly one row here. The table needs to capture every parameter that Oracle's UTL_MAIL.SEND accepts, plus some housekeeping columns so the listener can track which rows it's already processed, when they were sent, and what (if anything) went wrong. The table has a trigger that fires on new records when notify is TRUE.
A few design notes worth flagging:
-- Table utl_mail.mail_queue
CREATE TABLE utl_mail.mail_queue (
id SERIAL PRIMARY KEY,
created TIMESTAMP,
created_by TEXT,
sender VARCHAR(320),
recipients TEXT NOT NULL,
cc TEXT,
bcc TEXT,
subject VARCHAR(998),
message TEXT NOT NULL,
mime_type VARCHAR(255) DEFAULT 'text/plain; charset=us-ascii',
priority INTEGER DEFAULT 3,
replyto VARCHAR(320),
process_id BIGINT,
notify BOOLEAN DEFAULT FALSE,
sent_at TIMESTAMP
);
CREATE TRIGGER insert_notify
AFTER INSERT ON utl_mail.mail_queue
FOR EACH ROW
WHEN (NEW.notify IS TRUE)
EXECUTE FUNCTION utl_mail.notify_utl_mail();
Notice that an insert trigger is also included with the table definition. You will see how that is used later even though its purpose may be obvious.
This is where PostgreSQL's LISTEN/NOTIFY mechanism is used to inform a listener that there is a mail to send. Whenever a row lands in utl_mail.mail_queue, the trigger fires a notification on a named channel. This trigger is shown with the table definition above. Any process subscribed to that channel — in our case, the Python listener — gets woken up immediately and can fetch the new row.
The channel name we'll use is `$trigger$utl_mail$new_mail`. The dollar signs are deliberate — they make the channel name visually distinctive in logs and unlikely to collide with anything an application developer might name. Our naming convention is $source $table $event - you can change it to what you see as appropriate. The notification payload itself is intentionally tiny: just a label and the new row's record ID. The listener uses that ID to read the full record from the table. This keeps the notification stream well under the 8,000-byte pg_notify payload limit, and — just as importantly — keeps sensitive data like email addresses and message bodies out of the notification channel entirely.
-- Notify trigger function specific to this table
-- The notification/channel name can contain $ and _ with letters and numbers. We use '$source$table$event'
CREATE OR REPLACE FUNCTION utl_mail.notify_utl_mail()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
PERFORM pg_notify('$trigger$utl_mail$new_mail', NEW.id::text);
RETURN NULL;
END;
$$;
This is the compatibility layer. The function's signature matches Oracle's UTL_MAIL.SEND parameter-for-parameter, so the migration impact on calling PL/SQL is minimal. Internally, all the function does is insert a row into utl_mail.mail_queue. The trigger does the rest.
A subtle point worth raising: Oracle's UTL_MAIL.SEND has some default parameter values ('priority' defaults to 3, mime_type defaults to 'text/plain; charset=us-ascii'). PL/pgSQL supports default parameter values, so we can mirror that behaviour exactly. Existing Oracle callers that relied on defaults will work without modification.
-- pgplsql Function utl_mail.send
-- called by by other pgplsql code to replace utl_mail.send(...)
-- Oracle's utl_mail.send():
-- UTL_MAIL.SEND (
-- sender IN VARCHAR2 CHARACTER SET ANY_CS,
-- recipients IN VARCHAR2 CHARACTER SET ANY_CS,
-- cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
-- bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
-- subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
-- message IN VARCHAR2 CHARACTER SET ANY_CS,
-- mime_type IN VARCHAR2 DEFAULT 'text/plain; charset=us-ascii',
-- priority IN PLS_INTEGER DEFAULT 3,
-- replyto IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL
-- );
CREATE OR REPLACE PROCEDURE utl_mail.send(
sender TEXT,
recipients TEXT,
cc TEXT DEFAULT NULL,
bcc TEXT DEFAULT NULL,
subject TEXT DEFAULT NULL,
message TEXT DEFAULT '',
mime_type TEXT DEFAULT 'text/plain; charset=us-ascii',
priority INTEGER DEFAULT 3,
replyto TEXT DEFAULT NULL,
p_notify BOOLEAN DEFAULT TRUE,
p_created_by TEXT DEFAULT CURRENT_USER,
p_created TIMESTAMP DEFAULT now()::timestamp
)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO utl_mail.mail_queue (
sender,
recipients,
cc,
bcc,
subject,
message,
mime_type,
priority,
replyto,
notify,
created_by,
created
)
VALUES (
sender,
recipients,
cc,
bcc,
subject,
message,
mime_type,
priority,
replyto,
p_notify,
p_created_by,
COALESCE(p_created, now()::timestamp)
);
--COMMIT;
END;
$$;
The listener is the only piece that lives outside the database. It's a long-running Python process — typically run under systemd, supervisord, or whatever your operations team prefers — and it does two things:
You'll want to think carefully about how many listener instances to run. One is simplest but creates a single point of failure. Multiple listeners need row-level locking ('SELECT ... FOR UPDATE SKIP LOCKED') so the same message doesn't get sent twice. For more demanding workloads, two listeners behind a shared queue with 'SKIP LOCKED' is a good balance between resilience and complexity. However, a single listener will satisfy most production environments that have non-critical email delivery requirements as starting the script will send the backlog.
The below Python requires a minimum version of 3.8, however versions of the libraries referenced you install may require a later Python version.
It is 784 lines long, so we have included the comments and libraries only. The full script is available at https://gitlab.com/pebble-it/postgresql-utl_mail
#!/usr/bin/env python3
# utf-8
#
"""
pg_poll_mail.py
~~~~~~~~~~~~~~~
A PostgreSQL 'LISTEN/NOTIFY'-driven mail sender. Companion script
to the blog post *Closing the UTL_MAIL Gap: Sending Email from PostgreSQL
After an Oracle Migration*.
Description
-----------
Subscribes to a PostgreSQL notification channel raised by a trigger on
'utl_mail.mail_queue'. On startup it drains any unsent rows that
accumulated while the listener was down, then blocks waiting for new
'NOTIFY' events. For each row it claims (using
'SELECT ... FOR UPDATE SKIP LOCKED') it builds a MIME message, sends
it via SMTP, and marks the row as sent. HTML mail is auto-detected and
a plain-text alternative is attached alongside via
'multipart/alternative'.
Usage
-----
python3 pg_poll_mail.py [--debug] [--test] [--env-uri]
[--no-stop-on-error]
--debug Verbose logging.
--test Use a local SMTP debug server on localhost:1025
and force DEBUG logging. Pair with, for example:
'python3 -m aiosmtpd -n -l localhost:1025'
--env-uri Read the PostgreSQL URI from the 'PG_URI'
environment variable instead of GNOME Keyring.
Convenient for local development;
NOT appropriate for production.
--no-stop-on-error Continue processing the backlog after a per-row
failure (default: stop on first error so the
operator notices).
stdout / stderr are intended to be captured by 'systemd',
'supervisord', or similar when this script is deployed as a service.
Requirements
------------
pip install psycopg2-binary keyring html2text
Requires Python 3.8 or newer. The script uses 'dataclasses' (3.7+)
and 'typing.Literal' (3.8+). It has been developed and tested on
Python 3.12; nothing in the code is 3.12-specific, but 3.7 and 3.6
will refuse to run it. Operators on RHEL/Oracle Linux 8, whose system
Python is 3.6, should install a newer interpreter (the appstream
packages 'python3.9', 'python3.11', or 'python3.12' all work)
or use a 'pyenv' build.
Security model
--------------
The PostgreSQL URI is treated as sensitive. By default the script reads
it from GNOME Keyring, prompting the operator at startup for the master
password that unlocks the keyring entry. To create the entry once:
python3 -m keyring set pg_uri <MASTER_PASSWORD>
# then paste: postgresql://user:password@host:port/dbname
Only '--env-uri' (intended for development) reads from a plain
'PG_URI' environment variable. The URI is never logged in full; only
the host portion is logged at INFO level so the operator can confirm the
listener is pointed at the expected server.
Equivalent platform alternatives to GNOME Keyring: macOS Keychain,
Windows Credential Manager, HashiCorp Vault, AWS Secrets Manager,
Azure Key Vault. The 'keyring' library abstracts most of these.
Author
------
Stephen Alleyn, Pebble IT Solutions Pty Ltd, 2025.
Licence
-------
Permission is hereby granted to any person to use, copy, modify,
distribute, and reuse this software for any purpose, without
restriction and without attribution.
This software is provided "as is", without warranty of any kind, express
or implied, including but not limited to the warranties of
merchantability, fitness for a particular purpose, and non-infringement.
In no event shall the author be liable for any claim, damages, or other
liability, whether in an action of contract, tort, or otherwise, arising
from, out of, or in connection with the software or the use or other
dealings in the software.
"""
from __future__ import annotations
import argparse
import getpass
import logging
import os
import re
import select
import signal
import smtplib
import sys
import time
from dataclasses import dataclass
from datetime import datetime
from email.header import Header
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.utils import formatdate
from typing import List, Literal, Optional, Tuple
import html2text
import keyring
import psycopg2
import psycopg2.extensions
The Python listener shown above is deliberately minimal. It exists to illustrate that the architecture is sound and the moving parts fit together. Before any of this goes near production, the following should be on your backlog:
This script gives a strong example of how to solve the UTL_MAIL problem, how you implement is up to you and your requirements.
What this pattern gives you
Once these five components are in place, the migration story for UTL_MAIL becomes considerably simpler. Existing Oracle PL/SQL gets translated to PL/pgSQL using your migration tooling (we recommend world class tooling of Spectral Core's SQL Trans or Splendid Data's Cortex), and calls to UTL_MAIL.SEND(...) are rewritten as utl_mail.send(...). The function signature is the same. The behaviour, from the caller's perspective, is the same.
What you get for the trouble is arguably better than what Oracle offered in the first place:
Beyond the script-level to-do items above, there are some architectural trade-offs worth noting:
The gap between Oracle and PostgreSQL isn't usually in the things people talk about like data types and program units — it's in the small, convenient packages that Oracle includes and PostgreSQL deliberately doesn't. UTL_MAIL is one of the more common ones, but the pattern shown here — queue table, pg_notify, external listener — is reusable. The same architecture works for UTL_HTTP, UTL_FILE, DBMS_SCHEDULER external jobs, and most other "PL/SQL talks to the outside world" packages.
If you're planning an Oracle-to-PostgreSQL migration and you've started inventorying the Oracle-supplied package calls in your codebase, get in touch. We avoid going down the rabbit hole by using world class migration tools that take the risk out of migrations. We've done this work before, and we can help you scope it before it becomes a burden.
Pebble IT helps organisations modernise their data platforms. Talk to us about your migration.
All of the code from this article can be obtained from:
https://gitlab.com/pebble-it/postgresql-utl_mail
