Calling an Oracle Procedure that returns results over a linked server

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?
LVL 33
Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I bet if you look deeper into the issue the slowdown in performance is transmitting the data across the wire.  There might be a little extra overhead using native SQL but I wouldn't expect much.

I wouldn't expect passing the cursor results to be that much faster than the direct SQL.

I wouldn't expect SQL Server to be able to read an Oracle cursor natively.

Have you considered a Materialized View (MV) on the Oracle side?  Then you can issue a straight select form the MV and the MV can be as complex as you need.

raterusAuthor Commented:
I'm interested in returning a table of results from a SQL query.  The article you posted just seems to be returning an array of information.
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

slightwv (䄆 Netminder) Commented:
I'm not sure why you need dynamic sql.  As you know from your previous question, I'm not a SQL Server person.  Oracle has the ability to connect to non-Oracle databases with Heterogeneous Services.  These behave just like a 'normal' linked database.

I would be surprised if SQL Server cannot compete.

A little looking around came up with:

The query in that blog doesn't appear to be dynamic.
raterusAuthor Commented:
I don't have any issues connecting to Oracle, as the OLE DB provider works fine.  The problem is the oracle tables are large, and queries take much much longer using standard sql syntax, vs. running the same query directly on Oracle.  I was hoping to have the query execute on Oracle, and just pass the result set back, but seemingly there is no way for SQL to read an oracle cursor.
raterusAuthor Commented:
It's not a wire transmitting issue, I can send a select query to Oracle over the linked server (through SQL Server's OpenQuery), and the results come back near instantly.

I've experimented with creating views in oracle, that perform the select/joins, then on SQL Server I put in the where, but I still notice a slow down.  It's not as bad using this method, but it's still much slower than sending the full query to oracle.

As I mentioned in my first question on this subject, a query that involved quite a few joins took 8 1/2 minutes to execute using native SQL over the linked server, however when I run the same thing on oracle, the results popped up instantly.
slightwv (䄆 Netminder) Commented:
Verify the timing you observe.  When I hear things like "the results popped up instantly"  I get concerned.

Some tools/utilities start displaying results as soon as they can but are actually still generating/running the code.

Is it possible the query over the link requires the entire result before it shows anything where the tool you are using to time this in Oracle does not?

Try the query from the simplest tool possible to query Oracle: sqlplus.

If possible log directly into the database server and run it local.

Then run this:

set timing on
set term off
set pages 0
spool junk
select ... -- your select
spool off

That should (if I remembered everything) create a file called junk.lst.  The last line in that file should be the time it took to create/spool all the data.
raterusAuthor Commented:
Once I got the notion of using stored procedures out of my head, I experimented more with views and I'm getting great performance over the linked server.  I create an oracle view with lots of joins, then just select from SQL Server filling in the where clause.  It's working great!
slightwv (䄆 Netminder) Commented:
Glad to help!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.