Migrating Oracle to SQL Server using SSMA - Error O2SS0029 Cannot convert EXIT statement
By Bill Ramos and Mayank Bhanawat, Advaiya Inc.
This blog post describes why SQL Server Migration Assistant (SSMA) for Oracle doesn’t convert the EXIT statement when an outer loop is exited from inside the scope of inner loop. The LOOP statement is used to define a loop with an indeterminate number of iterations. The EXIT statement exits a loop and transfers control immediately to the end of the loop.
Error O2SS0029 Cannot convert EXIT statement
Background
Whenever an outer loop is exited from inside the scope of inner loop, SSMA cannot always convert the exit statement and generates following error: ‘ Error O2SS0029 Cannot convert EXIT statement’
Possible Remedies
Consider the example below in which outer loop is exited from inside the scope of inner loop.
DECLARE
s PLS_INTEGER := 0;
i PLS_INTEGER := 0;
j PLS_INTEGER;
BEGIN
<<outer_loop>>
LOOP
i := i + 1;
j := 0;
<<inner_loop>>
LOOP
j := j + 1;
s := s + i * j; -- sum a bunch of products
DBMS_OUTPUT.PUT_LINE('i: ' || TO_CHAR(i));
DBMS_OUTPUT.PUT_LINE('j: ' || TO_CHAR(j));
DBMS_OUTPUT.PUT_LINE('s: ' || TO_CHAR(s));
EXIT inner_loop WHEN (j > 5);
EXIT outer_loop WHEN ((i * j) > 15);
END LOOP inner_loop;
END LOOP outer_loop;
DBMS_OUTPUT.PUT_LINE('The sum of products equals: ' || TO_CHAR(s));
END;
When SSMA tries to convert the above code, it generates following error: “Error O2SS0029 Cannot convert EXIT statement”.
The solution of the above problem is, first close the inner loop (END LOOP inner_loop;) and then EXIT the outer loop as shown below in the highlighted code:
DECLARE
s PLS_INTEGER := 0;
i PLS_INTEGER := 0;
j PLS_INTEGER;
BEGIN
<<outer_loop>>
LOOP
i := i + 1;
j := 0;
<<inner_loop>>
LOOP
j := j + 1;
s := s + i * j; -- sum a bunch of products
DBMS_OUTPUT.PUT_LINE('i: ' || TO_CHAR(i));
DBMS_OUTPUT.PUT_LINE('j: ' || TO_CHAR(j));
DBMS_OUTPUT.PUT_LINE('s: ' || TO_CHAR(s));
EXIT inner_loop WHEN (j > 5);
END LOOP inner_loop;
EXIT outer_loop WHEN ((i * j) > 15);
END LOOP outer_loop;
DBMS_OUTPUT.PUT_LINE('The sum of products equals: ' || TO_CHAR(s));
END;
When you execute the above code in SSMA, it executed successfully. But in this query, the loop iterates one time less than source example because in the resolution we have first closed the inner loop and then EXIT the outer loop.
The resolution of this problem is to use the GOTO approach in SQL Server. The GOTO statement is used to transfer control of program execution to the statement that has a specified statement label. For this, we need to update the SQL Server code as follows:
BEGIN
DECLARE
@s int = 0,
@i int = 0,
@j int
condition1:
WHILE (@i * @j) > 15 break
BEGIN
SET @i = @i + 1
SET @j = 0
condition2:
WHILE 1 = 1
BEGIN
PRINT 's: ' + ISNULL(CAST(@s AS varchar(max)), '')
IF ((@i * @j) > 15) BREAK
SET @j = @j + 1
SET @s = @s + @i * @j
IF (@j > 5) goto condition1
PRINT 'i: ' + ISNULL(CAST(@i AS varchar(max)), '')
PRINT 'j: ' + ISNULL(CAST(@j AS varchar(max)), '')
IF ((@i * @j) > 15) goto condition2
END
PRINT 'The sum of products equals: ' + ISNULL(CAST(@s AS varchar(max)), '')
END
END
GO
References
For more information, check out the following references: