Edit

Share via

O2SS0205: Unable to convert PRAGMA AUTONOMOUS_TRANSACTION (Error)

This article covers some of the reasons why SQL Server Migration Assistant (SSMA) for Oracle doesn't convert the PL/SQL blocks containing PRAGMA AUTONOMOUS_TRANSACTION statement on the block level (that is, standalone BEGIN ... END).

Background

Autonomous Transactions allow you to create a new sub-transaction that might commit or roll back changes independent of the parent transaction. PRAGMA refers to a compiler directive, and is used to provide an instruction to the compiler.

In Oracle, when you define a PL/SQL block as an Autonomous Transaction, it isolates the DML in that block from the caller's transaction context. The block becomes an independent transaction started by another transaction, referred to as the main transaction. To mark a PL/SQL block as an autonomous transaction, include the following statement in your declaration section:

PRAGMA AUTONOMOUS_TRANSACTION;

In the previous statement, PRAGMA can perform an autonomous transaction within a PL/SQL block between a BEGIN and END statement without affecting the entire transaction.

Whenever SSMA tries to convert the PL/SQL block with PRAGMA AUTONOMUS_TRANSACTION statement (that is, standalone BEGIN ... END), it doesn't support PRAGMA on the block level. This is because SSMA can't find its corresponding object in SQL Server. But SSMA can convert autonomous transactions at the routine level (that is, for procedures, functions etc.).

Example

Consider the following example:

DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    FOR i IN 3 .. 10 LOOP
        INSERT INTO at_test (id, description)
        VALUES (i, 'Description for ' || i);
    END LOOP;

    COMMIT;
END;

When SSMA tries to convert the previous PL/SQL block, it gives following error:

O2SS0205: Unable to convert PRAGMA AUTONOMUS_TRANSACTION

Possible remedies

To convert a PL/SQL block with an AUTONOMOUS_TRANSACTION statement, you should wrap the code into a procedure, function, or trigger. In the previous example, you can define a procedure to insert the data into the at_test table as an autonomous transaction. The solution to the previous issue is as follows:

CREATE OR REPLACE PROCEDURE Insert_Data
AS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    FOR i IN 3 .. 10 LOOP
        INSERT INTO at_test (id, description)
        VALUES (i, 'Description for ' || i);
    END LOOP;

    COMMIT;
END;

How SSMA converts an autonomous transaction

To convert the previous Autonomous Transaction statement, SSMA uses the extended stored procedure emulator xp_ora2ms_exec2_ex. After SSMA creates the autonomous block as separate procedure, it calls this procedure using the xp_ora2ms_exec2_ex emulator as shown in the following example.

BEGIN
    DECLARE
        @active_spid INT,
        @login_time DATETIME

    SET @active_spid = ssma_oracle.get_active_spid()
    SET @login_time = ssma_oracle.get_active_login_time()

    EXECUTE master.dbo.xp_ora2ms_exec2_ex
        @active_spid,
        @login_time,
        DB_NAME(),
        N'DBO',
        N'INSERT_DATA$IMPL',
        N'false'
END

Internally xp_ora2ms_exec2_ex connects back to the same instance of SQL Server using current credentials and executes the given stored procedure (INSERT_DATA$IMPL in the previous sample). Since it's a separate connection, modifications done by the executed stored procedure can be committed or rolled back separately from the calling transaction.

Since new connection has to be opened to SQL Server, such conversion is relatively slow, compared to native Oracle implementations. Whenever possible, rewrite the code without the Autonomous Transaction.

  • O2SS0292: PRAGMA AUTONOMUS_TRANSACTION converted
  • O2SS0308: PRAGMA AUTONOMOUS_TRANSACTION ignored