Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Since a pipelined result set cannot be returned over a database link (see MOS note 560743.1), what workarounds/alternatives are there. I can think of a few, but I'd rather not lead the question. I am particularly interested in alternatives that work when the function would accept more than one parameter and return more than one row of data.

share|improve this question
    
The MetaLink doc mentions "Release: 9.2 to 11.1" - can you confirm it is still the case for 11.2? –  Jack Douglas Jul 25 '11 at 18:26
    
As far as I know no changes were made in 11.2 to allow this to work. –  Leigh Riffel Jul 25 '11 at 20:39
add comment

2 Answers

up vote 2 down vote accepted

Hoo boy - that's not going to be simple, as far as I know.

A few things I guess you could try:

  • Drop a view on top and stream those results across the link. The problem is going to be passing parameters though...
  • Have a routine across the link that you can pass parameters to that dumps your pipelined results to a table that you can then stream across the link. Then perhaps have a cleanup function that nukes the table when you're done with it (but beware concurrency...)
  • Re-architect? Instead of pipelining rows, you could return a collection of records (not sure how that translates across the link, though -- never tried it). Alternatively, a method whereby you could call it several times, each time returning the values of a row in a record. Neither is particular elegant. (You'd have to have some sort of start/next function at least, if not more.)

Pipelining is such a useful feature that it is a shame it doesn't translate across the db-link, though. Maybe Oracle will, one day, see fit to fill that particular gap. Otherwise, though, I fear you've got your work cut out for you.

share|improve this answer
    
I had thought of your second option. A global temporary table makes it a bit more viable by making cleanup / concurrency automatic, but it is still quite a hassle. –  Leigh Riffel Jul 25 '11 at 18:33
add comment

XML is one option but it doesn't scale well if you are bringing back lots of data.

I think Advanced Queueing/Streams uses ANYDATASET as a neutral transfer mechanism but it wouldn't be pretty

share|improve this answer
    
Unfortunately in the particular case that prompted this question the data is already in XML format, but I wanted the developer to be able to avoid parsing the XML in multiple databases that are accessing this one. –  Leigh Riffel Jul 26 '11 at 12:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.