Condividi tramite

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

  • Articolo

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

Comments

  • Anonymous
    June 05, 2013
    Hi Bill! 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. -- Sincerely, Thiago