PRAGMA AUTONOMOUS_TRANSACTION
Survived by a workaround.
✓ certified by the mortician
In life
An autonomous transaction is an independent transaction started from inside another one. A procedure, function or trigger declared with PRAGMA AUTONOMOUS_TRANSACTION suspends the calling transaction, does its own work, commits or rolls back on its own, and then lets the caller resume. It does not see the caller's uncommitted changes and does not share its locks.
Migrations care because the pattern is common for error logging, audit trails and counters that must survive a rollback of the main work, and neither Transact-SQL nor PL/pgSQL has an equivalent construct. Emulations exist (table variables that ignore rollback on SQL Server, a loopback connection on PostgreSQL) but each changes semantics, so every autonomous unit needs a design decision rather than a mechanical rewrite.
The feature is a core part of PL/SQL and is not deprecated. Oracle's Concepts guide describes it under transactions, noting that committed changes become visible to other sessions immediately and that autonomous units can nest.
Cause of departure
Not deprecated by Oracle. Listed here because Azure offers no clean equivalent.
Obituary
PRAGMA AUTONOMOUS_TRANSACTION, born before 11.2, let PL/SQL routines conduct work independently of the caller’s transaction. It was especially handy when a side effect needed to outlive a rollback.
Its Azure passage was blocked on Managed Instance, where unsupported extended stored procedures prevent SSMA’s second-connection emulator from being installed. Anonymous blocks fare no better: SSMA declines them with O2SS0205.
It is survived by Azure Database for PostgreSQL Flexible Server, only by workaround, and Azure SQL Managed Instance, only by workaround. A contested account advises treating every conversion as a manual rewrite and checking converted T-SQL for xp_ora2ms_exec2_ex before deployment.
Survived by
Azure Database for PostgreSQL Flexible Server workaround effort M
PostgreSQL has no autonomous transactions; procedures can COMMIT only when called at top level, and functions cannot commit at all. Both Ora2Pg and Microsoft's schema conversion tool emulate the pragma by running the body over a second connection with dblink (Ora2Pg optionally with pg_background), which works but adds a connection per call and bypasses the caller's snapshot.
- Keep AUTONOMOUS_TRANSACTION enabled in Ora2Pg (or accept the tool's dblink wrapper) for a first working port.
- Allowlist dblink on the Flexible Server and secure its connection string.
- Replace high-frequency logging autonomous routines with plain inserts committed by the caller, or with an external log.
- Load-test, since each wrapped call opens a new session.
Complications
Azure SQL Managed Instance workaround effort L
SQL Server has no autonomous transaction: nested BEGIN TRAN calls join the outer transaction. SSMA emulates the pragma at routine level by splitting the routine into an implementation procedure and a wrapper that calls an extended stored procedure to open a second connection, but Managed Instance does not support extended stored procedures, so this emulation cannot be deployed there. Logging-style autonomous transactions are better replaced with table variables (which survive rollback) or an external log sink.
- List routines with PRAGMA AUTONOMOUS_TRANSACTION and classify them (audit logging, error logging, counters, business logic).
- For logging, write to a table variable and persist after the outer transaction ends, or send to Azure Monitor / Event Hubs.
- For business logic, restructure so the independent work is a separate call from the application.
- Avoid the SSMA extended-procedure emulator on PaaS targets; rewrite instead.
Complications
Contested accounts
Does SSMA's autonomous-transaction emulation run on Managed Instance?
SSMA reports PRAGMA AUTONOMOUS_TRANSACTION in procedures and functions as converted, because it emulates the pragma with an extended stored procedure that opens a second connection. Microsoft's own list of T-SQL differences says Azure SQL Managed Instance does not support extended stored procedures at all. One Microsoft tool marks as done what another Microsoft platform cannot host.
SSMA does not convert PRAGMA AUTONOMOUS_TRANSACTION in anonymous blocks (error O2SS0205); for procedures and functions it emulates the pragma by calling the extended stored procedure xp_ora2ms_exec2_ex in master, which opens a second connection to run the split-out implementation procedure and is documented as slow compared with Oracle.
Azure SQL Managed Instance does not support extended stored procedures (sp_addextendedproc and sp_dropextendedproc are unavailable), so SSMA's extended-procedure based autonomous transaction emulator cannot be installed there and converted routines that depend on it must be rewritten.
Resolution. Treat every 'converted' autonomous transaction as a manual rewrite when the target is Managed Instance or Azure SQL Database. Typical replacements: write audit rows through a separate connection from the application tier, use table variables (which survive a rollback) to carry log data out of the failing transaction, or move the side effect to a Service Broker activation procedure on Managed Instance. Verify by searching the converted T-SQL for xp_ora2ms_exec2_ex before deploying. (T-SQL differences between SQL Server and Azure SQL Managed Instance)
Notices of correction
- oracle Transactions (Database Concepts 19c) · Database Concepts 19c, E96138-11, Sep 2025 · read 2026-09-26
- community PostgreSQL documentation: Transaction management in PL/pgSQL · PostgreSQL 18 · read 2026-09-26
- community Ora2Pg documentation (configuration directives) · Ora2Pg 25.0 (changelog 2025-04-20); text read from doc/Ora2Pg.pod in the GitHub repository · read 2026-09-26
- microsoft Oracle to Azure Database for PostgreSQL schema conversion (VS Code PostgreSQL extension) · page updated 2026-08-04 · read 2026-09-26
- microsoft SSMA for Oracle: O2SS0205 Unable to convert PRAGMA AUTONOMOUS_TRANSACTION · page updated 2025-05-27 · read 2026-09-26
- microsoft T-SQL differences between SQL Server and Azure SQL Managed Instance · page updated 2026-09-09 · read 2026-09-26
- microsoft SSMA for Oracle: Converting Oracle Schemas · SSMA for Oracle, page updated 2026-08-24 · read 2026-09-26