By Bill Ramos and Badal Bordia, Advaiya Inc.
This blog post describes why SQL Server Migration Assistant (SSMA) for Oracle doesn’t convert the dynamic string within an OPEN...FOR statement and presents a natural alternative to returning cursors by using a result set returned by Transact-SQL procedures.
The OPEN-FOR statement implements the query associated with a cursor variable and assigns database resources to process the query and recognizes the result set. A CURSOR is a mechanism by which you can assign a name to a "SELECT statement" and manipulate the information within that SQL statement.
In order to convert the OPEN … FOR cursor using SSMA, first you need to set the parameters in the SSMA project settings. Details about the relevant project settings are available in the blog post “Error O2SS0094 Unable to convert CURSOR as parameter”.
If you have any dynamic string in your code, the SSMA tool will generate the following error message: “OPEN ... FOR statement will be converted, but the dynamic string must be converted manually”.
Consider the below example that you might find in an Oracle procedure:
FOR 'Select ename from emp';
When SSMA tries to convert the above code, it gives the error “Error O2SS0157 Dynamic string for OPEN...FOR not converted”.
There are two possible remedies for correcting the dynamic string error:
1. Remove the single quotes from the dynamic query to make it a static query and run SSMA against the code again. Below is the modified Oracle code:
FOR Select ename from emp;
SSMA will generate the following block of Transact-SQL Code:
SET @emp_refcur =
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. In this approach, unlike Oracle, there is no need of using any cursor as output parameters.
For demonstrating this, let’s take the code of previous example i.e.
SSMA generates the following corresponding SQL code. This code will simply return a cursor, which carries the reference of the values in the ename column of the emp table.
SQL Server stored procedures are designed to return one or more result sets without having to define a cursor to handle the results. By executing the query used for the Oracle cursor inside of the stored procedure, you can process the result set in your application code.
Consider the following Transact-SQL stored procedure that can emulate the original Oracle dynamic SQL example:
ALTER PROCEDURE dbo.P_CURSOR_PROC
SET @query = 'Select ename from emp'
EXECUTE sp_executesql @query
You can use an application written in PHP or ADO.NET to execute the query and process the results. You can refer to the MSDN help topic How to: Execute a Stored Procedure that Returns Rows for details. There is also a good tutorial on using C# from http://www.csharp-station.com – Lesson 07: Using Stored Procedures. For a PHP example, check out - http://www.devarticles.com/c/a/PHP/Executing-SQL-Server-Stored-Procedures-With-PHP/3/.
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 O2SS0330/Error O2SS0331: Unable to convert close/ FETCH statement
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
· MSDN help topic How to: Execute a Stored Procedure that Returns Rows