O2SS0013: EXECUTE IMMEDIATE statement was converted, but dynamic string was not converted. It must be converted manually.

Question

Hi,

I've checked common conversion errors for Oracle Database to SQL Server 2016 migration with SSMA (https://learn.microsoft.com/en-us/sql/ssma/oracle/messages/o2ss0007?view=sql-server-ver16) however this error is missingO2SS0013, EXECUTE IMMEDIATE statement was converted, but dynamic string was not converted. It must be converted manually. Below you find the details of the error. Can someone help me convert this and show how to convert it so that the procedure GETSEQUENCEVALUE can work and be migrated to the SQL Server 2016 and Data can be migrated?

ORACLE Procedure on Windows 2003 server:

 

CREATE OR REPLACE 
PROCEDURE getSequenceValue 
(
SeqName VARCHAR2, 
nValue IN OUT Number
)
AS
BEGIN 
EXECUTE IMMEDIATE 'SELECT ' || SeqName || '.NEXTVAL FROM DUAL'
INTO nValue;
END;

 

 

SSMA ERROR for SQL Migration:

 

CREATE PROCEDURE GETSEQUENCEVALUE  
@SEQNAME varchar(max),
/*
*   SSMA warning messages:
*   O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/

 

@NVALUE float(53)  OUTPUT
AS 
BEGIN

 

/* 
*   SSMA error messages:
*   O2SS0013: EXECUTE IMMEDIATE statement was converted into EXEC(...) statement, but dynamic string was not converted. It must be converted manually.
*

 

EXECUTE ('SELECT ' + ISNULL(@SEQNAME, '') + '.NEXTVAL FROM DUAL')
*/

 

 

DECLARE
@db_null_statement int

 

END
GO

Would love to hear from you, thanks!

Answer 1

Hi 65877627,

Can you try with that?

Oracle Procedure:

CREATE OR REPLACE
PROCEDURE getSequenceValue
(
  SeqName VARCHAR2,
  nValue IN OUT Number
)
AS
BEGIN
  EXECUTE IMMEDIATE 'SELECT ' || SeqName || '.NEXTVAL FROM DUAL'
  INTO nValue;
END;

SQL Server 2016 Procedure:

CREATE PROCEDURE GETSEQUENCEVALUE
(
  @SEQNAME varchar(max),
  @NVALUE float(53) OUTPUT
)
AS
BEGIN
  -- SSMA warning: Conversion from NUMBER datatype can cause data loss.

  -- SSMA error: EXECUTE IMMEDIATE statement was converted into EXEC(...) statement, but dynamic string was not converted. It must be converted manually.

  DECLARE @sqlText nvarchar(max);

  SET @sqlText = N'SELECT ' + @SEQNAME + '.NEXTVAL FROM DUAL';

  EXEC sp_executesql @sqlText, N'@SEQNAME varchar(max)', @SEQNAME, @NVALUE OUTPUT;
END;

Regards,

Answer 2

The code suggested by Osjaetor will not work. There are a number of errors in it.

However, rather than trying to correct those errors, I suggest scapping the procedure altogether.

To get the next value from a sequence, just say

SELECT @x = NEXT VALUE FOR pelle