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