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;

 

Error_O2SS0094_01

 

To resolve this error, you should perform the following steps:

1.    Click the Tools menu and select Project Settings.

Error_O2SS0094_02

2.    Then, click the General tab.

Error_O2SS0094_03

 

3.       Select Yes for the Convert OPEN-FOR statements for REF CURSOR OUT parameters option, apply these settings, and then, click OK.

Error_O2SS0094_04

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.

Error_O2SS0094_05

 

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:

Migrating Oracle to SQL Server 2008 White Paper