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.
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:
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.).
Consider the following example:
FOR i IN 3 .. 10 LOOP
INSERT INTO at_test (id, description)
VALUES (i, 'Description for ' || i);
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
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.
There are several other errors related to Autonomous Transaction that you may encounter. These include the following:
For more information, check out the Migrating Oracle to SQL Server 2008 White Paper.
Hello Bill and Mayank,
Really nice blog on SSMA.