By Bill Ramos and Ankit Matta, Advaiya Inc.

This blog post describes why SQL Server Migration Assistant (SSMA) for Oracle doesn’t convert the statement having Cursor attributes with any conditional operator.

A CURSOR is a mechanism by which you can assign a name to a "SELECT statement" and manipulate the information within that SQL statement. Cursors are used by database programmers to process individual rows returned by database system queries.

Before embarking on the migration of Oracle Cursors, you should consider the “Migrating Oracle Cursors” section of the Migrating Oracle to SQL Server 2008 White Paper.

Error O2SS0265 Unable to convert condition (with cursor attributes)

Background

Although SSMA facilitates emulation of various Cursor attributes, it generates Error O2SS0265 when it encounters Cursor statement with some conditional logic, like IS NULL, etc.

Possible Remedies

Consider the example below that has been designed to check whether the CURSOR values are fetched or not. Also, the below code will give you the list of the entire employee numbers from the EMP table.

DECLARE

      emp_no emp.empno%TYPE;

      CURSOR c1 IS select empno from emp;

BEGIN

   OPEN c1;

    IF (c1%FOUND is null) THEN

      DBMS_OUTPUT.PUT_LINE('Cursor Is Not Found');

    Else

      DBMS_OUTPUT.PUT_LINE('Cursor Found');

    END IF;

     LOOP

       FETCH c1 INTO emp_no;

       EXIT WHEN c1%NOTFOUND;

       DBMS_OUTPUT.PUT_LINE(emp_no);

     END LOOP;

   CLOSE c1;

END;

When SSMA tries to convert the above code, it gives the error: “Error O2SS0265: Unable to convert condition”

Error_O2SS0265_01

The solution of the above error is to rewrite the conditional block in the SQL Server Metadata Explorer after converting the code using SSMA. Majority of the code gets converted as there is only one conditional block. You have to only modify some logic so as to have a corresponding SQL code. The FOUND attribute is converted to “IF @@FETCH_STATUS = 0” and the NOTFOUND attribute is converted to “IF @@FETCH_STATUS <> 0”. Below is the manually converted code:

BEGIN

DECLARE

      @emp_no numeric(4, 0)

DECLARE

      c1 CURSOR LOCAL FOR 

      SELECT EMP.EMPNO

      FROM dbo.EMP

      OPEN c1

      IF (@@FETCH_STATUS) = 0

BEGIN

      PRINT 'Cursor Found'

END

ELSE 

      PRINT 'Cursor Is Not Found'

WHILE 1 = 1

BEGIN

      FETCH c1 INTO @emp_no  

      IF @@FETCH_STATUS <> 0

      BREAK

      PRINT @emp_no

END

      CLOSE c1

      DEALLOCATE c1

END

GO

 

Error_O2SS0265_02 

In general practice, the Cursor should be fetched before doing any operation on CURSOR attributes. So for the above example, just type “FETCH c1 INTO @emp_no” before doing any operation on the cursor.

Related Errors

There are several other errors related to “CURSOR” that you may encounter. These include the following:

·         Error O2SS0094: Unable to convert CURSOR as parameter

·         Error O2SS0245: CURSOR conversion in return statements not supported

References

For more information, check out the following references:

Migrating Oracle to SQL Server 2008 White Paper