Migrating Oracle to SQL Server using SSMA - Error O2SS0274 Call to function with cursor return value cannot be converted in current context
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”.
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: