Migrating Oracle to SQL Server using SSMA - O2SS0264 Unable to convert cursor or cursor variable as a function or procedure call parameter

  • Article

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

Background

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”

Possible Remedies

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.

 

Procedure:

CREATE OR REPLACE PROCEDURE p_close_refcursor

(

emp_refcur OUT SYS_REFCURSOR

)

AS

test_cursor SYS_REFCURSOR;

departmentno dept.deptno%TYPE;

   BEGIN

      OPEN test_cursor

         FOR

            SELECT deptno

            FROM dept;

      LOOP

         FETCH test_cursor

            INTO departmentno;

          EXIT WHEN test_cursor%NOTFOUND;

         DBMS_OUTPUT.PUT_LINE(departmentno);

      END LOOP;

      emp_refcur := test_cursor;

      CLOSE test_cursor;

END;

 

Statement:

DECLARE

emp_cur SYS_REFCURSOR;

BEGIN

p_close_refcursor(emp_cur);

END;

 

When we execute the above statement in SSMA, it generate the Error ‘O2SS0264’ as shown in the below figure.

 

clip_image001

 

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:

Procedure:

CREATE PROCEDURE dbo.P_CLOSE_REFCURSOR 

   @emp_refcur Cursor Varying OUTPUT

AS

  

   BEGIN

 

      --SET @emp_refcur = NULL

 

      DECLARE

         @test_cursor CURSOR,

         @departmentno float(53)

 

      SET @test_cursor =

         CURSOR FOR

            SELECT DEPT.DEPTNO

            FROM dbo.DEPT

 

      OPEN @test_cursor

 

      WHILE 1 = 1

 

        BEGIN

            FETCH @test_cursor

                INTO @departmentno

                  IF @@FETCH_STATUS <> 0

               BREAK

            PRINT @departmentno

         END

 

      SET @emp_refcur = @test_cursor

      CLOSE @test_cursor

      DEALLOCATE @test_cursor

 

   END

Go

 

Now you can use the below code to call the above mentioned procedure by passing the cursor variable

Statement:

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

References

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