Edit

Share via

O2SS0245: CURSOR conversion in return statements not supported (Error)

This article covers the reason why SQL Server Migration Assistant (SSMA) for Oracle can't convert some of the statements that have cursor as a return type in any function.

Background

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 SSMA, the conversion of cursors in return statements isn't supported. So if a function is defined with a cursor as return type, then SSMA generates an error message.

Example

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 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 SSMA migrates this function, it generates the following error:

O2SS0245: CURSOR conversion in return statements not supported

Possible remedies

One possible remediation is to create and use temporary table inside the function of SQL Server instead of using a cursor. Then the reference of this temporary table is returned by the function.

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