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]

 

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

· 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