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
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:
For more information, check out the following references: