Migrating Oracle to SQL Server using SSMA - Error O2SS0330/ O2SS0331: Unable to convert CLOSE/FETCH statement

[アーティクル]
06/20/2011

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

)

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.

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

)

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;

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”

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