Migrating Oracle to SQL Server using SSMA - Error O2SS0245: Cursor conversion in return statements not supported

By Bill Ramos and Badal Bordia, Advaiya Inc.

This blog post covers why SQL Server Migration Assistant (SSMA) for Oracle cannot convert some of the statements that have cursor as a return type in any function. 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.

Error O2SS0245: Cursor conversion in return statements not supported

Background

In SSMA, the conversion of cursors in return statements is not supported. So if a function is defined with a cursor as return type, then SSMA generates O2SS0245 error.

Possible Remedies

Consider the following example of having a package PackageName, containing a function func_name. This function returns the cursor out_cursor defined inside the package.

CREATE OR REPLACE PACKAGE PackageName AS

TYPE Ref_cursor IS REF CURSOR RETURN emp%ROWTYPE;

Function func_name (choice INT) return Ref_cursor;

END PackageName;

The function is defined as:

CREATE OR REPLACE Function PackageName$func_name(choice in INT)

    Return PackageName.ref_cursor

 As

    out_cursor PackageName.Ref_cursor;

 

Begin

 IF choice = 1 THEN

  OPEN out_cursor FOR SELECT * FROM emp WHERE comm IS NOT NULL;

 ELSIF choice = 2 THEN

  OPEN out_cursor FOR SELECT * FROM emp WHERE sal > 2500;

 ELSIF choice = 3 THEN

  OPEN out_cursor FOR SELECT * FROM emp WHERE deptno = 20;

 END IF;

 

    Return out_cursor;

End;

 

When migrating this function, SSMA generates the error O2SS0245: Cursor conversion in return statements not supported.

 

 

One possible remediation is to create and use temporary table inside the function of SQL Server instead of using cursor. And then the reference of this temporary table is returned by the function. For this, we need to update the SQL Server code as follows:

 

 

CREATE FUNCTION dbo.func_name

(

    @Choice int

)

RETURNS @TempEmp TABLE

(

      [EMPNO] [numeric](4, 0) NOT NULL,

      [ENAME] [varchar](10) NULL,

      [JOB] [varchar](9) NULL,

      [MGR] [numeric](4, 0) NULL,

      [HIREDATE] [datetime2](0) NULL,

      [SAL] [numeric](7, 2) NULL,

      [COMM] [numeric](7, 2) NULL,

      [DEPTNO] [numeric](2, 0) NULL,

      [ROWID] [uniqueidentifier] NOT NULL

)

 

AS

BEGIN

      IF (@Choice=1)

      BEGIN

            INSERT INTO @TempEmp

            SELECT *

            FROM EMP where COMM is NOT NULL;

      END

     

      IF (@Choice=2)

      BEGIN

                  INSERT INTO @TempEmp

                  SELECT *

                  FROM EMP where sal > 2500;

      END

     

      IF (@Choice=3)

      BEGIN

                  INSERT INTO @TempEmp

                  SELECT *

                  FROM EMP WHERE deptno = 20;

      END

RETURN;

 

END

 

 

Related Errors

There are several other errors related to “Collection” that you may encounter. These include the following:

· Error O2SS0094 Unable to convert CURSOR as parameter.

References

For more information, check out the following references.

· Migrating Oracle to SQL Server 2008 White Paper