executing an oracle stored procedure from Sql Server 2008
for a simple Oracle procedure call from SQL Server
exec ('begin sproc_name; end;') at linked_server_name
calling procedure with variables
<B>declare @z int<B><B>set @z = 10 <B>exec ('begin sproc_name(''' + @z + '''); end;') at linked_server_name;This works fine for me use
Passing output variables through result set should work:
DECLARE @X nvarchar(255) = '';DECLARE @Y int = 0;DECLARE @Z int = 2;DECLARE @Result As Table (X nvarchar(255), Y int);INSERT INTO @Result (X, Y) EXEC('declare X nvarchar(255) = ?; Y int = ?; Z int = ?; begin USER.PKG.TEST(X, Y, Z); select X, Y from DUAL; end;', @X, @Y, @Z) AT DB_ORACLE;SELECT @X = X, @Y = Y FROM @Result;