Search code examples
sql-serveroracle-databaselinked-server

Calling an Oracle function from SQL Server Linked Server

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