Calling an Oracle function from SQL Server Linked Server

Question

I have setup a linked server pointing to an Oracle DB. I want to call a function ABC in a package XYZ passing parameter K. What will be the syntax to do this?

Solution

I used the following syntax and it worked for me.

EXECUTE (Query, Parameters) AT LinkedServerName

Example:

EXECUTE ( 'BEGIN ? := Package.MyFunction(?,?); END;', @ReturnValue, @InputPara, @OutputPara OUTPUT ) AT LinkedServerName

Important Points:

• Don't forget the BEGIN and END syntax when calling functions
• Don't forget the semicolon at the end "END**;**"
• For using the above syntax you need to enable RPC for the linked server
• Oracle will not consider the call as a function call unless you accept the output in a variable