I am using the following T-SQL query in SQL server 2005 (Management Studio IDE):
DECLARE @id int;
DECLARE @countVal int;
DECLARE @sql nvarchar(max);
SET @id = 1000;
SET @sql = 'SELECT COUNT(*) FROM owner.myTable WHERE id = @id';
EXEC (@sql) AT oracleServer -- oracleServer is a lined server to Oracle
I am not sure how to pass the input parameter @id to the EXEC query, and pass the count result out to @countVal. I saw some examples for Microsoft SQL server like:
EXEC (@sql, @id = @id)
I tried this for Oracle but I got a statement error:
OLE DB provider "OraOLEDB.Oracle" for linked server "oracleServer"
returned message "ORA-00936: missing expression"