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

Question

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

it 89526

SSC Enthusiast

Points: 124
More actions
October 6, 2020 at 6:50 am

#3793092
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
May 18, 2021 at 6:42 am

This was removed by the editor as SPAM

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

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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 1

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

it 89526 SSC Enthusiast Points: 124 More actions · Answer 2

Thank you Grant.

Just hopped to find the magic solution 🙂

Andreas

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

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: 90252 More actions · Answer 4

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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