Migrating Oracle to SQL Server using SSMA - Error O2SS0205 Unable to convert PRAGMA AUTONOMUS_TRANSACTION

  • Article

By Bill Ramos and Mayank Bhanawat Advaiya Inc.

This blog post 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 (i.e. standalone BEGIN … END).

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

Error O2SS0205 Unable to convert PRAGMA AUTONOMUS_TRANSACTION

Background

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, simply include the following statement in your declaration section:

   PRAGMA AUTONOMOUS_TRANSACTION;

In the above 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 Autonomous_Transaction statement (i.e. standalone BEGIN … END), it does not support PRAGMA on the block level. This is because SSMA cannot find its corresponding object in the SQL Server. But SSMA can convert autonomous transactions at the routine level (i.e. for procedures, functions etc.).

Possible Remedies

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 above PL/SQL block, it gives following error:

“O2SS0205: SQL Server Migration Assistant for Oracle Error message: Unable to convert PRAGMA AUTONOMUS_TRANSACTION”

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

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 Using xp_ora2ms_exex_ex

To convert the above Autonomous transaction statement, SSMA uses the extended stored procedure emulator xp_ora2ms_exec2_ex. After creating the autonomous block as separate procedure, SSMA calls this procedure using the xp_ora2ms_exec2_ex emulator as shown below.

Error O2SS0205_1

There are several other errors related to Autonomous Transaction that you may encounter. These include the following:

  • O2SS0292 PRAGMA AUTONOMUS_TRANSACTION converted
  • O2SS0308 PRAGMA AUTONOMOUS_TRANSACTION ignored

References

For more information, check out the Migrating Oracle to SQL Server 2008 White Paper.