How to pass result out of exec a query on oracle db?

Question

I have the following call to Oracle db:

DECLARE @myCount int;
DECLARE @sql NVARCHAR;
SET @sql = N'SELECT COUNT(*) FROM owner.myTable';
EXEC (@sql) AT oracleServer
-- how to get result count to @myCount?

where oracleServer is a linked server to Oracle. How can I pass the count result out to a varaible @myCount? I tried:

EXEC (@sql, @myCount output) AT oracleServer;

it is not working. I must miss something or different way to get output result?

Answer 1

Your select statement needs to look like this:

SELECT @myCount = COUNT(*) FROM owner.myTable