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

  • it 89526

    Valued Member

    Points: 74

    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

  • Grant Fritchey

    SSC Guru

    Points: 396763

    Yep. Impossible. You simply can't combine database management systems in this way. They're not compatible. To solve a problem like this, you'd need to create some type of service to consume the data. CLR could do it.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • it 89526

    Valued Member

    Points: 74

    Thank you Grant.

    Just hopped to find the magic solution 🙂

    Andreas

  • Erland Sommarskog

    SSC-Insane

    Points: 23947

    There is one alternative. But it is worse than the CLR. You can call extended stored procedures from SQL functions, so you can call xp_cmd.... I think I stop there.

    (I think the real bummer here is not the strict design of functions  in SQL Server, but the fact that OPENQUERY does not accept parameter markers like EXEC AT does.)

     

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Jeffrey Williams

    SSC Guru

    Points: 88707

    It sounds like maybe Service Broker would be an option here.  I think with a service broker it can be setup to call a stored procedure - where you can then use OPENQUERY to query Oracle to get the stock.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply