EXEC(query) AT linkedServer With Oracle DB

Question

I am using Microsoft SQL server 2005. I need to sync data between SQL server and an Oracle db. First thing I need is to find out if the count of data on Oracle side with certain filters(here I use ID as a simple example).

SELECT COUNT(*) FROM oracleServer..owner.table1 WHERE id = @id;

The problem I have is that the table on the lined server or Oracle is very big with 4M rows of data. The above query took about 2minutes to get data back. This code is just a simplied piece. Actually my SP has some other queries to update, insert data from the lined server to my SQL server. The SP took hours or 10+ hours to run with large Oracle db. Therefore T-SQL with lined server is not good for me.

Recently I found OPENQUERY and EXEC (...) AT linedServer. OPENQUERY() is very fast. It took about 0 time to get the same result. However, it does not support variable query or expressions. The query has to be a literal constant string.

EXEC() is in the same way to pass-through query to Oracle. It is fast as well. For example:

EXEC ('SELECT COUNT(*) FROM owner.table1 WHERE id = ' + CAST(@id AS VARCHAR))
  AT oracleServer

The problem I have is how to pass the result COUNT(*) back. I tried to google examples in web and msdn. All I can find are SQL or ExpressSQL linedServer examples like:

EXEC ('SELECT ? = COUNT(*) FROM ...', @myCount OUTPUT) AT expressSQL

This query does not work for Oracle. It seems in Oracle, you can set value as output in this way:

SELECT COUNT(*) INTO myCount ...

I tried this:

EXEC ('SELECT COUNT(*) INTO ? FROM ...', @myCount OUTPUT) AT oracleServer
EXEC ('SELECT COUNT(*) INTO : FROM ...', @myCount OUTPUT) AT oracleServer
EXEC ('SELECT : = COUNT(*) FROM ...', @myCount OUTPUT) AT oracleServer

None of those working. I got error message saying query not executable on Oracle server.

I could write a .Net SQL Server project to do the job. Before that, I just wonder if there is anyway way to pass value out as oupput parameter so that I put the better performance T-SQL codes in my SP?

Answer 1

Just a quick update on this. I think I got the solution. I found it in a discussion on a similar issue at Dev NewsGroup. Based on the information, I tried this:

DECLARE @myCount int;
DECLARE @sql nvarchar(max);
set @sql =
N'BEGIN 
  select count(*) into :myCount from DATAPARC.CTC_MANUAL_DATA; 
END;'
EXEC (@sql, @myCount OUTPUT) AT oracleServer;
PRINT @myCount; -- 3393065

Wa! I got the result back in 3 seconds comparing T-SQL query directly on Orable DB (+2minutes). The important thing is to use "BEGIN" and "END;" to wrap the query as anonymous block and don't miss ";" after END

You need anonymous block for output parameters. If you only have input or no parameters, you don't need the block and the query works fine.

Enjoy it! By the way, this is a quick update. If you don't see me again, I would not have any trouble on this issue.