Edit

Share via

O2SS0274: Call to function with cursor return value cannot be converted in current context (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:

CREATE OR REPLACE FUNCTION F_RETURN_CUR
RETURN SYS_REFCURSOR
IS
    refCursorValue SYS_REFCURSOR;
BEGIN
    OPEN
        refCursorValue
    FOR
        SELECT * FROM dept;

    RETURN (refCursorValue);
END;
/

DECLARE
    refCursorValue SYS_REFCURSOR;
    myRecord dept%ROWTYPE;
BEGIN
    refCursorValue := F_RETURN_CUR;

    LOOP
        FETCH refCursorValue
        INTO myRecord;

        EXIT WHEN refCursorValue%NOTFOUND;

        dbms_output.put_line(refCursorValue%ROWCOUNT);
    END LOOP;
END;

When you execute the previous code in Oracle, a function with name F_RETURN_CUR is created. This function returns the cursor refCursorValue. While SSMA migrates this function, it generates an error message:

O2SS0274: Call to function with cursor return value cannot be converted in current context

Possible remedies

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

Update the SQL Server code as follows:

CREATE FUNCTION dbo.F_RETURN_CUR()
RETURNS @TempEmp TABLE
(
   [DEPTNO] [numeric](4, 0) NOT NULL,
   [DNAME] [varchar](10) NOT NULL
)
AS
BEGIN
    INSERT INTO @TempEmp
        SELECT * FROM dept;

    RETURN;
END
GO

BEGIN
    DECLARE
        @v_refCursorValue_rowcount int,
        @refCursorValue CURSOR

    DECLARE
        @myRecord$DEPTNO float(53),
        @myRecord$DNAME varchar(20)

    DECLARE
        @TableVariable TABLE (DEPTNO INT, DNAME NVARCHAR)

    SET @refCursorValue =
        CURSOR FOR
        SELECT * FROM F_RETURN_CUR()

    OPEN @refCursorValue

    SET @v_refCursorValue_rowcount = 0

    WHILE 1 = 1
    BEGIN
        FETCH @refCursorValue
        INTO @myRecord$DEPTNO, @myRecord$DNAME

        IF @@FETCH_STATUS = 0
            SET @v_refCursorValue_rowcount = @v_refCursorValue_rowcount + 1

        IF @@FETCH_STATUS <> 0
            BREAK

        PRINT @v_refCursorValue_rowcount
    END
END