SQL Server to Oracle Linking Part 2(Contd..)

This post is the continuation of my previous posting about SQL Oracle linking. There were several issues associated with the SQL and external database linking.

Performance is very poor when the external database call has Joins and Where clauses.

Invoking calls to tables which have synonym set up requires manual configuration for distributed schema. (i.e. Applications will have different schema names in different environments like Dev/Test and Prod. Querying the tables with different schema names requires change in schema names in the different environments)

These issues can be resolved through OpenQuery class of SQL Server. The OpenQuery executes a query on the linked server. Although the ‘Query’ returns multiple rows, the OpenQuery returns only the first row.
OPENQUERY ( linked_server ,'query' ).

The DML statements can be invoked as given below.
Declare @ OracleSqlStatement1 Varchar(Max)
Select
SET @OracleSqlStatement = 'SELECT UserEmail FROM OPENQUERY(Link_Server,,''select eMail from TestTableA''where UserID = '''''+ @ UserID +''''' '')'
Insert
SET @OracleSqlStatement1= 'insert into openquery(Link_Server,''select * from TestTableA'')(UserID, eMail) values ('''+ @UserID +''', '''+ @UserEmail +''')'

Update
SET @OracleSqlStatement1= 'update openquery(Link_Server,''select * from TestTableA where UserID = '''''+ @ UserID +''''' '')
SET EMAIL = '''+ @UserEmail +''' '
Delete
DELETE OPENQUERY (Link_Server, ''SELECT name FROM TestTableA WHERE UserID = '''''+ @ UserID +''''' '');

The OpenQuery defined above can be executed by calling a SQL method EXEC