Send Emails like Oracle UTL_SMTP using pg_utl_smtp for PostgreSQL

As part of its automated migration solution HexaRocket, to simplify Enterprise-grade Oracle to PostgreSQL database migrations, HexaCluster is pleased to announce a new PostgreSQL extension dedicated to Oracle compatibility.

When you migrate Oracle PL/SQL mail-sending code from Oracle to PostgreSQL, one of the interesting friction point is usually UTL_SMTP compatibility - you want the same call flow, similar parameters, and predictable failure behavior. pg_utl_smtp has been created by HexaCluster specifically for that, bringing Oracle UTL_SMTP-style procedures into PostgreSQL, so you can keep the migration timelines small. This article documents what pg_utl_smtp supports, what it does not support, and the practical UTL_SMTP compatibility limitations you should plan for, along with installation and usage instructions.

HexaRocket automatically adds compatibility to all such Oracle compatibility functionalities automatically, so, do not hesitate to ask us for a demo on HexaRocket.

Beyond supporting migrations, this extension allows email notifications to be sent from triggers or stored procedures. By integrating the extension directly into the database, you can trigger data-driven actions without waiting for application intervention. Examples include security alerts and notifications when certain minimum thresholds are reached or to send reports directly from stored procedures.

pg_utl_smtp for Oracle UTL_SMTP compatibility

When you migrate PL/SQL mail-sending code from Oracle UTL_SMTP to PostgreSQL, one of the interesting friction point is usually UTL_SMTP compatibility - you want the same call flow, similar parameters, and predictable failure behavior. pg_utl_smtp exists specifically for that, bringing Oracle UTL_SMTP-style procedures into PostgreSQL, so you can keep the migration timelines small. This article documents what pg_utl_smtp supports, what it does not support, and the practical UTL_SMTP compatibility limitations you should plan for, along with installation and usage instructions.

To install and get started with pg_utl_smtp, please follow the README.

Supported Oracle UTL_SMTP routines in pg_utl_smtp

Today, pg_utl_smtp implements these Oracle UTL_SMTP routines (procedural forms). This list was observed as the core SMTP happy-path needed for most send a simple email use cases, and it matches the typical operational sequence documented for Oracle UTL_SMTP.

• OPEN_CONNECTION
• EHLO
• HELO
• MAIL
• RCPT
• OPEN_DATA
• WRITE_DATA
• WRITE_RAW_DATA
• CLOSE_DATA
• QUIT

Not Supported (Yet) Oracle UTL_SMTP routines in pg_utl_smtp

These Oracle UTL_SMTP routines are not available yet in pg_utl_smtp. If you rely on any of those for your current Oracle UTL_SMTP implementation, let us know.

Here's a real-world example of implementing the extension using the following Oracle procedure.

PROCEDURE send_mail 
( p_to        IN VARCHAR2, 
 p_from      IN VARCHAR2, 
 p_subject   IN VARCHAR2, 
 p_text_msg  IN VARCHAR2 DEFAULT NULL, 
 p_html_msg  IN VARCHAR2 DEFAULT NULL, 
 p_smtp_host IN VARCHAR2, 
 p_smtp_port IN NUMBER DEFAULT 25) 
AS 
 l_mail_conn   UTL_SMTP.connection; 
 l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*='; 
BEGIN 
 l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port); 
 UTL_SMTP.helo(l_mail_conn, p_smtp_host); 
 UTL_SMTP.mail(l_mail_conn, p_from); 
 UTL_SMTP.rcpt(l_mail_conn, p_to); 
 UTL_SMTP.open_data(l_mail_conn); 
 
 UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf); 
 UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf); 
 UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf); 
 UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf); 
 UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf); 
 UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf); 
 UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf); 
 
 IF p_text_msg IS NOT NULL THEN 
   UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf); 
   UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf); 
   utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(p_text_msg)); 
   UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf); 
 END IF; 
 
 IF p_html_msg IS NOT NULL THEN 
   UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf); 
   UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf); 
 
   UTL_SMTP.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(p_html_msg)); 
   UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf); 
 END IF; 
 UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf); 
 UTL_SMTP.close_data(l_mail_conn); 
 
 UTL_SMTP.quit(l_mail_conn); 
END; 

Converting this code using the pg_utl_smtp extension

You can now observe the same functionality and syntax compatibility to Oracle, in the following PostgreSQL procedure.

CREATE OR REPLACE PROCEDURE send_mail ( 
   p_to text, 
   p_from text, 
   p_subject text, 
   p_text_msg text DEFAULT NULL, 
   p_html_msg text DEFAULT NULL, 
   p_smtp_host text DEFAULT NULL, 
   p_smtp_port bigint DEFAULT 25 
) AS $body$ 
DECLARE 
 l_mail_conn   UTL_SMTP.connection; 
 l_boundary    varchar(50) := '----=*#abc1234321cba#*='; 
 
BEGIN 
 
 l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port); 
 CALL UTL_SMTP.helo(l_mail_conn, p_smtp_host); 
 CALL UTL_SMTP.mail(l_mail_conn, p_from); 
 CALL UTL_SMTP.rcpt(l_mail_conn, p_to); 
 
 CALL UTL_SMTP.open_data(l_mail_conn); 
 
 CALL UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(clock_timestamp(), 'DD-MON-YYYY HH24:MI:SS') || '\n'); 
 CALL UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || '\n'); 
 CALL UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || '\n'); 
 CALL UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || '\n'); 
 CALL UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || '\n'); 
 CALL UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || '\n'); 
 CALL UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || '\n' || '\n'); 
 
 IF p_text_msg IS NOT NULL THEN 
   CALL UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '\n'); 
   CALL UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || '\n' || '\n'); 
   CALL utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(p_text_msg)); 
   CALL UTL_SMTP.write_data(l_mail_conn, '\n' || '\n'); 
 END IF; 
 
 IF p_html_msg IS NOT NULL THEN 
   CALL UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '\n'); 
   CALL UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || '\n' || '\n'); 
 
   CALL UTL_SMTP.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(p_html_msg)); 
   CALL UTL_SMTP.write_data(l_mail_conn, '\n' || '\n'); 
 END IF; 
 
 CALL UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || '\n'); 
 CALL UTL_SMTP.close_data(l_mail_conn); 
 
 UTL_SMTP.quit(l_mail_conn); 
END; 
$body$ 
LANGUAGE PLPGSQL 
SECURITY DEFINER 
; 

HexaCluster has developed and maintains several Oracle compatibility extensions to simplify your database migrations to PostgreSQL. Following are some of such extensions that can be very useful while you are migrating from Oracle to PostgreSQL, to avoid spending time on adding these features or creating workarounds.

• pg_dbms_job: emulates Oracle's scheduler DBMS_JOB.
• pg_dbms_errlog: This is an extension to emulate Oracle's DBMS_ERRLOG module for data manipulation error logging (LOG ERRORS INTO).
• pg_statement_rollback: This is an extension to add server side transaction with rollback at statement level like in Oracle or DB2. You can see the Oracle implicit behavior https://docs.oracle.com/en/database/oracle/oracle-database/26/zzpre/defining-controlling-transactions.html#GUID-95768D5C-6964-47D2-A033-A41810677953.
• pg_dbms_metadata: This is an extension to extract DDL of PostgreSQL database objects in a way compatible with Oracle DBMS_METADATA package. This extension allows familiarity to PostgreSQL for Oracle experts by using Oracle syntax in PostgreSQL, and also saves time in migrating any objects using this Oracle package.
• pg_dbms_lock: This is an extension to manage advisory locks in a way compatible with Oracle DBMS_LOCK package.
• oracle_fnd: extension to propose a solution to avoid rewriting calls to Oracle FND_GLOBAL and FND_PROFILE functions.
• pgtt: An extension to create, manage and use Oracle-style Global Temporary Tables and the others RDBMS.
• external_file: adds the same functionalities given by the Oracle's BFILE data type that stores unstructured binary data in flat files outside the database.

Subscribe to our Newsletters and Stay tuned for more interesting topics.

Conclusion

Many enterprise applications designed for Oracle use UTL_SMTP to send automatic notifications. This extension from HexaCluster avoids rewriting all the email sending logic in the application layer, thus reducing database migration time and cost.

If you need expert support migrating legacy or complex Oracle, SQL Server, Sybase, MySQL, MariaDB databases to PostgreSQL or distributed databases, we’re here to help.

HexaCluster provides end-to-end migration and modernization services, including application migration and modernization, database migration, and PostgreSQL consulting such as performance tuning, health audits, managed DBA services, and 24/7/265 support.

To start a conversation or explore how we can support your migration journey, please contact us at connect@hexacluster.ai