Another way execute Oracle stored procedure on MS SQL Server:
declare @result varchar(255)
exec ('BEGIN ?:= your_SP_Name(''arg1'', ''arg2'', etc); END; ',@result OUTPUT) at your_LinkedServerName;
select @result;
Or:
blogs.msdn.com/b/joaquinv/archive/2008/10/23/execute-oracle-stored-procedure-in-sql-server.aspx
Package Header:
PROCEDURE TestProcedure
(
I_Parameter1 IN NUMBER,
I_Parameter2 IN NUMBER,
O_Parameter1 OUT NUMBER,
O_Parameter2 OUT NUMBER
);
Package Body:
PROCEDURE TestProcedure
(
I_Parameter1 IN NUMBER,
I_Parameter2 IN NUMBER,
O_Parameter1 OUT NUMBER,
O_Parameter2 OUT NUMBER
) AS
BEGIN
O_Parameter1 := I_Parameter1 + 1;
O_Parameter2 := I_Parameter2 + 1;
END TestProcedure;
Then we create a SQL Server Stored Procedure to Wrapp the Oracle Store Procedure Invocation, the T-SQL inside the procedure for this sample is the following:
DECLARE
@l_i_parameter1 INTEGER
DECLARE @l_i_parameter2 INTEGER
DECLARE @l_o_parameter1 INTEGER
DECLARE @l_o_parameter2 INTEGER @l_i_parameter1 = 5
SET @l_i_parameter2 = 10
SET @l_o_parameter1 = 0
SET @l_o_parameter2 = 0('begin SDB.TestProcedure(?,?,?,?); end;', @l_i_parameter1, @l_i_parameter2, @l_o_parameter1 OUTPUT, @l_o_parameter2 OUTPUT) AT DBLINK_NAME;
SET
EXECUTE
SELECT @l_o_parameter1, @l_o_parameter2