Conversion of Dynamic Statement Execution from Microsoft SQL Server to Oracle

The EXECUTE sp_executesql statement is used to execute dynamic SQL statements with parameters in Microsoft SQL Server. The second string parameter of EXECUTE sp_executesql describes the dynamic SQL statement parameters and their types.

The EXECUTE IMMEDIATE statement is used to execute dynamic SQL statements with parameters in Oracle. Input parameters are marked as :N, where N is the number of the parameter in the USING clause.

SQLWays converts the Microsoft SQL Server EXECUTE sp_executesql statement to Oracle EXECUTE IMMEDIATE. SQLWays changes input parameters in MSQL dynamic statements to the appropriate Oracle syntax. SQLWays replaces parameter names in Microsoft SQL Server dynamic string to the number of the parameter when converting to Oracle.

TABLE 42. Conversion of Dynamic Statement Execution from Microsoft SQL Server to Oracle
Microsoft SQL Server	Oracle
create procedure sql_sp_executesql as declare @param int execute sp_executesql N'select * from tab1 where col1 = @param', N'@param int', @param = 35	CREATE OR REPLACE PROCEDURE sql_sp_executesql AS v_param NUMBER(10,0); BEGIN EXECUTE IMMEDIATE 'select * from tab1 where col1 = :1' USING 35; end;
create procedure sql_sp_executesql2 as declare @InsOrderID int declare @InsertString varchar(50) SET @InsertString = N'INSERT INTO tab1' + ' VALUES (@InsOrderID)' EXEC sp_executesql @InsertString, N'@InsOrderID INT', @InsOrderID	CREATE OR REPLACE PROCEDURE sql_sp_executesql2 AS v_InsOrderID NUMBER(10,0); v_InsertString VARCHAR2(50); BEGIN v_InsertString := 'INSERT INTO tab1' \|\| ' VALUES (:1)'; EXECUTE IMMEDIATE v_InsertString USING v_InsOrderID; END;
create procedure sql_sp_executesql3 as declare @val int declare @InsertString varchar(50) SET @InsertString = N'DELETE FROM tab1 WHERE col1 = @par1 and col2=@par2' EXEC sp_executesql @InsertString, N'@par1 INT, @par2 INT', @par1=1, @par2=@val	CREATE OR REPLACE PROCEDURE sql_sp_executesql3 AS v_val NUMBER(10,0); v_InsertString VARCHAR2(50); BEGIN v_InsertString := 'DELETE FROM tab1 WHERE col1 = :1 and col2=:2' ; EXECUTE IMMEDIATE v_InsertString USING 1,v_val; end;

TABLE 42. Conversion of Dynamic Statement Execution from Microsoft SQL Server to Oracle

Microsoft SQL Server

Oracle

create procedure sql_sp_executesql as
declare @param int
execute sp_executesql           N'select * from tab1 where 
col1 = @param',
N'@param int',
@param = 35

CREATE OR REPLACE PROCEDURE sql_sp_executesql AS
v_param NUMBER(10,0);
BEGIN
EXECUTE IMMEDIATE 'select * from tab1 where col1 = :1' 
USING 35;
end;

create procedure sql_sp_executesql2 as
declare @InsOrderID int
declare @InsertString varchar(50)
SET @InsertString = N'INSERT INTO tab1' +
' VALUES (@InsOrderID)'
EXEC sp_executesql @InsertString,
N'@InsOrderID INT',   @InsOrderID

CREATE OR REPLACE PROCEDURE sql_sp_executesql2 AS
v_InsOrderID NUMBER(10,0);
v_InsertString VARCHAR2(50);
BEGIN
v_InsertString := 'INSERT INTO tab1' ||
' VALUES (:1)';
EXECUTE IMMEDIATE v_InsertString USING v_InsOrderID;
END;

create procedure sql_sp_executesql3 as
declare @val int
declare @InsertString varchar(50) 
SET @InsertString = N'DELETE FROM tab1 WHERE 
col1 = @par1 and col2=@par2'
EXEC sp_executesql @InsertString,
N'@par1 INT, @par2 INT',   @par1=1, @par2=@val

CREATE OR REPLACE PROCEDURE sql_sp_executesql3 AS
v_val NUMBER(10,0);
v_InsertString VARCHAR2(50);
BEGIN
v_InsertString := 'DELETE FROM tab1 WHERE col1 = :1 and 
col2=:2' ;
EXECUTE IMMEDIATE v_InsertString
USING 1,v_val;
end;

Ispirer SQLWays Database Migration Software

Conversion of Dynamic Statement Execution from Microsoft SQL Server to Oracle