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:

·         Migrating Oracle to SQL Server 2008 White Paper