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.
When converting a procedure or function containing the REF CURSOR type passed as an argument, SSMA generates the O2SS0094 error.
Consider the following example:
CREATE OR REPLACE PROCEDURE p_cursor_proc
emp_refcur OUT SYS_REFCURSOR
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.
There are several other errors related to “CURSOR” that you may encounter including the following:
· Error O2SS0245 - Cursor conversion in return statements not supported
For more information, check out the following references: