Migrating Oracle to SQL Server using SSMA - Error O2SS0245: Cursor conversion in return statements not supported
By Bill Ramos and Badal Bordia, Advaiya Inc.
This blog post covers 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 O2SS0245: Cursor conversion in return statements not supported
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 O2SS0245 error.
Possible Remedies
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 PackageName$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 migrating this function, SSMA generates the error O2SS0245: Cursor conversion in return statements not supported.
![clip_image002[4]](https://msdntnarchive.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/01/39/83/metablogapi/6052.clip_image0024_166DD4D5.jpg "clip_image002[4]")
One possible remediation is to create and use temporary table inside the function of 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.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
Related Errors
There are several other errors related to “Collection” that you may encounter. These include the following:
· Error O2SS0094 Unable to convert CURSOR as parameter.
References
For more information, check out the following references.