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

  • Question

  • Hi,

    I have been trying too many hours 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?

    Thank you, Andreas

    Tuesday, October 6, 2020 8:22 AM