Migrating Oracle to SQL Server by using SSMA - Error O2SS0094 Unable to convert CURSOR as parameter
By Bill Ramos and Ankit Matta, Advaiya Inc.
This blog post covers why SQL Server Migration Assistant (SSMA) for Oracle cannot convert a procedure that contains the CURSOR as parameter.
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 O2SS0094 Unable to convert CURSOR as parameter
Background
When converting a procedure or function containing the REF CURSOR type passed as an argument, SSMA generates the O2SS0094 error.
Possible Remedies
Consider the following example:
CREATE OR REPLACE PROCEDURE p_cursor_proc
(
emp_refcur OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN emp_refcur
FOR
SELECT deptno
FROM dept;
END;
To resolve this error, you should perform the following steps:
1. Click the Tools menu and select Project Settings.
2. Then, click the General tab.
3. Select Yes for the Convert OPEN-FOR statements for REF CURSOR OUT parameters option, apply these settings, and then, click OK.
4. Try to convert the query again after making the change and you will find that it is converted easily.
The SSMA conversion option Convert OPEN-FOR statements for REF CURSOR OUT parameters is used because there is an ambiguity when a REF CURSOR output parameter is opened in the procedure. The REF CURSOR might be fetched in the caller procedure or used directly by the application (SSMA can handle this if the option is set to YES).
After converting this query, SSMA generates a warning message O2SS0259: CURSOR type was converted to VARCHAR (8000) . You can generally ignore this warning.
Related Errors
There are several other errors related to “CURSOR” that you may encounter including the following:
· Error O2SS0245 - Cursor conversion in return statements not supported
References
For more information, check out the following references: