Edit

Share via

O2SS0094: Unable to convert CURSOR as parameter (Error)

This article covers the reason why SQL Server Migration Assistant (SSMA) for Oracle can't convert a procedure that contains the CURSOR as parameter.

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.

When SSMA converts a procedure or function containing the REF CURSOR type passed as an argument, it generates an error message.

Example

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;

When you try to convert the previous code in SSMA, it generates the following error message:

O2SS0038: Hierarchical query not converted

Possible remedies

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

  1. In SSMA, navigate to Tools menu and select Project Settings.

  2. Then, select the General tab.

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

  4. When you try to convert the query again after making the change, it converts successfully.

The SSMA conversion option Convert OPEN-FOR statements for REF CURSOR OUT parameters is used because there's 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 scenario if the option is set to Yes).

Note

After converting this query, SSMA generates a warning message O2SS0259: CURSOR type was converted to VARCHAR (8000). You can generally ignore this warning.