I have an oracle query that I need to execute over a sql server linked server. I know I can just type the query into OPENQUERY and get results, however, I hate the way I must use dynamic sql, and the query ends up being very unreadable.
I thought it would be simple enough to create an oracle stored procedure or function that would return results, then I could call that from SQL Server using OpenQuery.
I've hit a brick wall though, I got both a function and procedure that will return results in Oracle (via an oracle cursor), but I can't figure out how to call this from SQL Server. I'm beginning to think this isn't possible when the return type is a cursor. I've seen the table return type, but it seems I have to define all the columns that will be returned, which I'd rather not have to do.
Can someone who has done this give me some hints as to how I can pull this off easily?