Migrating Oracle to SQL Server using SSMA - Error O2SS0157 Dynamic string for OPEN...FOR not converted
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”.
![clip_image001[4]](https://msdntnarchive.z22.web.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/01/39/83/metablogapi/6560.clip_image0014_454DAC11.jpg "clip_image001[4]")
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
GO
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
AS
BEGIN
DECLARE
@query nvarchar(max)
SET @query = 'Select ename from emp'
EXECUTE sp_executesql @query
END
GO
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/.
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 O2SS0330/Error O2SS0331: Unable to convert close/ FETCH statement
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
· MSDN help topic How to: Execute a Stored Procedure that Returns Rows
Comments
- Anonymous
June 21, 2015
Can you please suggest what we need change in below SP to fix the 'dynamic string' error CREATE PROCEDURE dbo.GET_CURRENT /* * SSMA warning messages: * O2SS0356: Conversion from NUMBER datatype can cause data loss. / @p_uid float(53), @return_value_argument varchar(8000) OUTPUT AS /Generated by SQL Server Migration Assistant for Oracle version 6.0.0./ BEGIN DECLARE @v_rc CURSOR, @v_query varchar(100), / * SSMA warning messages: * O2SS0356: Conversion from NUMBER datatype can cause data loss. / @v_login_id float(53) SELECT @v_login_id = LOGIN.ID FROM dbo.Test WHERE Test.PERSON_REL_ID = @p_uid SET @v_query = 'SELECT id FROM Table_' + ISNULL(CAST(@v_login_id AS nvarchar(max)), '') DECLARE @auxiliary_cursor_definition_sql nvarchar(max) DECLARE @auxiliary_exec_param nvarchar(max) IF (cursor_status('variable', N'@v_rc') > -2) DEALLOCATE @v_rc / * SSMA error messages: * O2SS0157: The OPEN...FOR statement will be converted, but the dynamic string must be converted manually./ SET @auxiliary_cursor_definition_sql = 'SET @auxiliary_tmp_cursor = CURSOR LOCAL FOR ' + @v_query + '; OPEN @auxiliary_tmp_cursor' SET @auxiliary_exec_param = '@auxiliary_tmp_cursor cursor OUTPUT' EXECUTE sp_executesql @auxiliary_cursor_definition_sql, @auxiliary_exec_param, @v_rc OUTPUT / * SSMA error messages: * O2SS0245: The conversion of cursors in return statements is not supported.*/ RETURN @v_rc END GO