SQL Server to Oracle Linking - Part 3 (Contd..)

In the previous blog I wrote about executing oracle table commands from SQL server. The ugly part is passing the input parameter to the oracle queries. This can be restructred and optimized as below,

Select
SET @OracleSqlStatement = 'SELECT UserEmail FROM OPENQUERY(Link_Server,,''select eMail from TestTableA''where UserID = '''''+ @ UserID +''''' '')' can be written as

EXEC(‘SELECT UserEmail From TestTableA where UserId=?’, @UserIdInput) AT Link_Server

Insert

SET @OracleSqlStatement1= 'insert into openquery(Link_Server,''select * from TestTableA'')(UserID, eMail) values ('''+ @UserID +''', '''+ @UserEmail +''')'
can be written as

EXEC(‘Insert Into UserEmail(UserID, emailed) values(?,?), @UserIdInput, @emailInput) AT Link_Server


This resolves the issues with the linked server having synonym for the database schema.