Execute an Oracle stored procedure from a SQL Server stored procedure

I have a SQL Server s开发者_如何转开发tored proc that needs to send information to an Oracle stored proc.

How do I set this up? Should it be a linked server? And how do I write the code to pass and execute the stored proc?

a procedure "TestingProcedure" in Oracle server.

a linked Server "LinkedServer" in SQL Server.

call TestingProcedure in SQL server example:

EXECUTE ('begin TestingProcedure; end;') AT LinkedServer;

You could also use:

DECLARE @outputParameter int
EXEC ('BEGIN <procedureName>(?,?); END;', @inputParameter , @outputParameter OUTPUT) at <linkedServerName>

This allows capturing output parameters

This is the syntax that I would probably try from the start:

SELECT * FROM OPENQUERY(<linked server name>, '{CALL <oracle sp>}')

What I've seen (I have not tried this myself) is that you can also use OPENQUERY to call an Oracle function which can then call the stored procedure, or you can turn the stored procedure into a function. Your connection from SQL Server would then look something like this:

SELECT * FROM OPENQUERY(<linked server name>, 'SELECT <oracle function> FROM dual')

Turns out I ended up having to use Microsofts CLR and write my own little C# application in order to handle the variables being pushed between the servers. Here's what I did:

1. C# Application starts on a scheduled task in windows
2. Application executes the SQL Server stored procedure and gets the return values
3. Application then executes the Oracle stored procedure with appropriate values