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.
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.
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.
CURSOR c1 IS select empno from emp;
IF (c1%FOUND is null) THEN
DBMS_OUTPUT.PUT_LINE('Cursor Is Not Found');
FETCH c1 INTO emp_no;
EXIT WHEN c1%NOTFOUND;
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:
@emp_no numeric(4, 0)
c1 CURSOR LOCAL FOR
IF (@@FETCH_STATUS) = 0
PRINT 'Cursor Found'
PRINT 'Cursor Is Not Found'
WHILE 1 = 1
FETCH c1 INTO @emp_no
IF @@FETCH_STATUS <> 0
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.
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
For more information, check out the following references: