Piplelined Result Set over Database Link Alternatives

Question

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.

Answer 1

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.

Answer 2

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