By Bill Ramos and Mayank Bhanawat, Advaiya Inc.
This blog post describes why SQL Server Migration Assistant (SSMA) for Oracle does not convert the PLSQL block when a cursor or cursor variable is passed as a parameter to a function or procedure call.
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. In Oracle, SYS_REFCURSOR is used to pass cursors from and to a stored procedure
When a cursor or cursor variable is passed as a parameter to a function or procedure call, SSMA cannot convert that statement and generates following error “Error O2SS0264: Unable to convert cursor or cursor variable as a function or procedure call parameter”
Consider the below example query in which we have declared a variable as SYS_REFCURSOR and passed this variable as parameter to a procedure call.
CREATE OR REPLACE PROCEDURE p_close_refcursor
emp_refcur OUT SYS_REFCURSOR
EXIT WHEN test_cursor%NOTFOUND;
emp_refcur := test_cursor;
When we execute the above statement in SSMA, it generate the Error ‘O2SS0264’ as shown in the below figure.
To solve this error, first you can convert the procedure (P_CLOSE_REFCURSOR) in SQL Server using SSMA and do following modifications in SQL code:
1. When SSMA convert the Oracle procedure in SQL Server, it converted the CURSOR (@emp_refcur) type to varchar (8000). But in SQL server we can declare cursor data type in an OUTPUT Parameter in following way “@emp_refcur Cursor Varying OUTPUT”.
2. Also SSMA initializes the variable @emp_refcur (which was a of type varchar (8000)) with null value. So after changing its type we have to remove this initialization by commenting the statement (SET @emp_refcur = NULL).
For this, we need to update the SQL Server Transact-SQL code as follows:
CREATE PROCEDURE dbo.P_CLOSE_REFCURSOR
@emp_refcur Cursor Varying OUTPUT
--SET @emp_refcur = NULL
SET @test_cursor =
WHILE 1 = 1
IF @@FETCH_STATUS <> 0
SET @emp_refcur = @test_cursor
Now you can use the below code to call the above mentioned procedure by passing the cursor variable
Declare @cursor_variable cursor
Execute dbo.P_CLOSE_REFCURSOR @cursor_variable
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
· Error O2SS0157 Dynamic string for OPEN...FOR not converted
For more information, check out the following references:
· Migrating Oracle to SQL Server 2008 White Paper
· Conversion of Oracle REF CURSOR used as OUTPUT Parameter – A DB BEST Technologies blog
Firstly, thank you very much by the very useful and pragmatic trick you provided.
It helped a lot.
However, at my point of view, this proposal was not met:
"This blog post describes *WHY* SQL Server Migration Assistant (SSMA) for Oracle does not convert the PLSQL block when a cursor or cursor variable is passed as a parameter"
That is, the post (despite of its usability and accuracy) does not explained at all *why* (the reason by which) the SSMA tool is unable to automatically do that trick.
Anyway, you already helped by teaching a workaround for that issue.