Table of ContentsPreviousNext

Ispirer             Ispirer


Ispirer SQLWays Database Migration Software

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 of ContentsPreviousNext
Ispirer             Ispirer
Copyright © 1999-2015 Ispirer Systems Ltd. Ispirer and SQLWays are trademarks of Ispirer Systems Ltd. All other product names may be trademarks of the respective companies. All rights reserved.