By Bill Ramos and Vishal Soni, Advaiya Inc.

This blog post describes why SQL Server Migration Assistant (SSMA) for Oracle gives an error when converting the CLOSE and FETCH…INTO statements in a procedure where a SYS_REFCURSOR is returned as parameter.

In Oracle, SYS_REFCURSOR is used to pass cursors from and to a stored procedure

 

Error O2SS0330/O2SS0331: Unable to convert CLOSE/FETCH statement

Background

Whenever we declare a variable for SYS_REFCURSOR as a return type of the procedure and use the same variable with FETCH...INTO and CLOSE statement, it gives the following errors:

·         Error O2SS0330: Unable to convert CLOSE statement

·         Error O2SS0331: Unable to convert FETCH statement

Possible Remedies

Consider the below example query in which we have declared a variable as SYS_REFCURSOR, used as a return type of a procedure.

CREATE OR REPLACE PROCEDURE p_close_refcursor

      (

         emp_refcur OUT SYS_REFCURSOR

      )

   IS

      departmentno dept.deptno%TYPE;

   BEGIN

      OPEN emp_refcur

         FOR

            SELECT deptno

            FROM dept;

      LOOP

         FETCH emp_refcur

            INTO departmentno;

          EXIT WHEN emp_refcur%NOTFOUND;

         DBMS_OUTPUT.PUT_LINE(departmentno);

      END LOOP;

      CLOSE emp_refcur;

   END;

 

When we execute the above query in SSMA, it generate the errors as shown in the below figure.

Error O2S0330_01

There are two possible remedies for correcting this error:

1.       First method is to create and use a local cursor for doing all operations, and then passing its value to the returning cursor before closing it. For this, declare a new variable of SYS_REFCURSOR type which will be local to this procedure. Modify the code to use this local cursor to perform the required operation including FETCH...INTO and CLOSE statement. Before closing the local cursor, pass the value of this local cursor to the cursor defined in the parameter section of procedure i.e. emp_refcur in our example.

The solution for the above error is shown in the code below:  

CREATE OR REPLACE PROCEDURE p_close_refcursor

(

emp_refcur OUT SYS_REFCURSOR

)

AS

test_cursor SYS_REFCURSOR;

departmentno dept.deptno%TYPE;

   BEGIN

      OPEN test_cursor

         FOR

            SELECT deptno

            FROM dept;

      LOOP

         FETCH test_cursor

            INTO departmentno;

          EXIT WHEN test_cursor%NOTFOUND;

         DBMS_OUTPUT.PUT_LINE(departmentno);

      END LOOP;

      emp_refcur := test_cursor;

      CLOSE test_cursor;

   END;

Error O2S0330_02 

2.       Another way to solve this error is to use the natural approach followed in SQL Server which is returning the result set directly from executing stored procedures. This approach is explained in detail in another blog related to SSMA “Error O2SS0157 Dynamic string for OPEN...FOR not converted”

Related Errors

There are several other errors related to “CURSOR” that you may encounter. These include the following:

·         Error O2SS0094: Unable to convert CURSOR as parameter

·         Error O2SS0245: CURSOR conversion in return statements not supported

·         Error O2SS0157 Dynamic string for OPEN...FOR not converted

References

For more information, check out the following references:

·         Migrating Oracle to SQL Server 2008 White Paper

·         Conversion of Oracle REF CURSOR used as OUTPUT Parameter – A DB BEST Technologies blog