We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Calling an Oracle Procedure that returns results over a linked server

Medium Priority
613 Views
Last Modified: 2012-05-11
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?
Comment
Watch Question

Top Expert 2005

Author

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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:
http://www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/

The query in that blog doesn't appear to be dynamic.
Top Expert 2005

Author

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Top Expert 2005

Author

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.
Top Expert 2005

Author

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!
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Glad to help!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.