3

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.

  • 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
3

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.

  • 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
0

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

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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