Skip to content

Default Profile Resolution

Oracle's UTL_MAIL package has no argument for a connection endpoint at all — it sends through one fixed value, the instance-wide smtp_out_server database parameter, set once by a DBA and never named in any call. Migrated PL/SQL therefore has no way to pass a pg_relay_notifier profile name, even if it wanted to — UTL_MAIL.SEND(sender, recipients, ...) simply has no such parameter.

Every utl_mail.* function resolves a profile automatically, checked in order:

  1. pg_relay_notifier_utl_mail.default_profile — this extension's own option, letting you route UTL_MAIL-originated traffic through a profile distinct from what other, non-Oracle callers use as their default.
  2. pg_relay_notifier.default_profile — pg_relay_notifier's own general fallback (see the DBA Guide), used only if the option above is unset.
  3. Neither set → the call raises the same error compose() raises for any other caller: no profile specified and pg_relay_notifier.default_profile is not configured.
-- shim-specific override
SELECT pgrelay.set_option('pg_relay_notifier_utl_mail.default_profile', 'oracle_legacy_mailer');

-- OR, if you don't need a distinct profile for UTL_MAIL traffic specifically,
-- just set the general one and skip the extension-specific option entirely
SELECT pgrelay.set_option('pg_relay_notifier.default_profile', 'my_smtp_profile');

Why this is a genuine advantage over Oracle, not just a gap filled

smtp_out_server is one fixed endpoint, full stop — Oracle has never had a concept of switching between multiple named connections at compose time, let alone routing between fundamentally different transports. default_profile can point at any registered profile — an SMTP relay today, a Microsoft 365 mailbox tomorrow — with zero application code change. Migrated code that has only ever known UTL_MAIL.SEND(...) gains multi-profile, multi-transport routing it never had in Oracle, entirely transparently.

This is also a sharper capability than orafce_mail — a separate, unrelated extension that also implements a utl_mail schema — provides. orafce_mail is a single fixed SMTP endpoint per database instance (its own orafce_mail.smtp_server_url GUC, one set of credentials); there's no concept of multiple named endpoints there either, let alone switching between an on-prem relay and Microsoft 365 Graph. See Schema Collision for why the two extensions can't be installed together regardless.

Where the resolution actually happens

The two-tier check lives inside each utl_mail.* function itself — it reads pg_relay_notifier_utl_mail.default_profile and passes whatever it finds (including NULL, if unset) straight through as p_profile to pgrelay_notifier.send_mail()/send_mail_attach_raw()/send_mail_attach_text(). The second tier — falling back further to pg_relay_notifier.default_profile — is compose()'s own behaviour, not something this extension implements a second time; see the DBA Guide for exactly how that resolution works.