Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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