Migrating Oracle to SQL Server using SSMA - Error O2SS0157 Dynamic string for OPEN...FOR not converted

Article
06/20/2011

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.

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

Background

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”.

Possible Remedies

Consider the below example that you might find in an Oracle procedure:

Declare

emp_refcur SYS_REFCURSOR;

BEGIN

OPEN emp_refcur

FOR 'Select ename from emp';

Close emp_refcur;

END;

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:

Oracle:

Declare

emp_refcur SYS_REFCURSOR;

BEGIN

OPEN emp_refcur

FOR Select ename from emp;

Close emp_refcur;

END;

SSMA will generate the following block of Transact-SQL Code:

BEGIN

DECLARE

@emp_refcur CURSOR

SET @emp_refcur =

CURSOR FOR

SELECT EMP.ENAME

FROM dbo.EMP

OPEN @emp_refcur

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. 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.

Declare

emp_refcur SYS_REFCURSOR;

BEGIN

OPEN emp_refcur

FOR Select ename from emp;

END;

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.

BEGIN

DECLARE

@emp_refcur CURSOR

SET @emp_refcur =

CURSOR FOR

SELECT EMP.ENAME

FROM dbo.EMP

OPEN @emp_refcur

END

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

BEGIN

DECLARE

@query nvarchar(max)

SET @query = 'Select ename from emp'

EXECUTE sp_executesql @query

END

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 https://www.csharp-station.com – Lesson 07: Using Stored Procedures. For a PHP example, check out - https://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