By Bill Ramos and Vishal Soni, Advaiya Inc.

This blog post covers the reason 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 O2SS0274 Call to function with cursor return value cannot be converted in current context

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 Error O2SS0274.

Possible Remedies

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 above code in Oracle, a function with name “F_RETURN_CUR” is created. This function returns the cursor refCursorValue. While migrating this function, SSMA generates the error “Error O2SS0274 Call to function with cursor return value cannot be converted in current context”.

Error_O2SS0274_01 

 

One possible remediation is to create and use temporary table inside the function in 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.F_RETURN_CUR(@Choice int)

 

RETURNS @TempEmp TABLE

(

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

   [DNAME] [varchar](10) NOT NULL

)

AS

BEGIN 

   INSERT INTO @TempEmp

   SELECT * FROM dept;                

RETURN;

END

 

 

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(1)

    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

GO

Related Errors

There are several other errors related to returning a CURSOR that you may encounter. These include the following:

·         Error O2SS0245 - Cursor conversion in return statements not supported

·         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