Migrating Oracle to SQL Server using SSMA - Error O2SS0265 Unable to convert condition (with CURSOR attributes)
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”
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
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: