Impossible to create an SQL Server function that calls an Oracle function?

Question

I am trying to create an SQL Server Scalar Function that calls an Oracle function.

Imagine you have a function in Oracle named "Stock_Orcl" that returns the stock for a product passed in parameter and you want an SQL server function (named "Stock_sql") that calls the Oracle function above "Stock_Orcl".

To make it harder I do not want to make a CLR function.

After too much searching and digging, I dare to say that I think this is impossible. I did create an SQL server procedure with success.

It seems impossible to turn it to a function for 2 main reasons:

1. SQL server Functions cannot call procedures that contain exec statement
2. SQL server Functions cannot contain an openquery dynamic statement

Has anybody any experience with that?

Answer 1

Both the things you found are true. SQL Server functions cannot call anything which might perform an update or insert, such as dynamic SQL or open rowset on a target database. There is no way around that. Have you tried using a SQL Server stored procedure instead?