Re: AQ Nightmare
- From: "Connor McDonald" <mcdonald.connor@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 30 Dec 2008 21:58:40 +0900
On Tue, Dec 23, 2008 at 6:44 AM, Brady, Mark
<Mark.Brady@xxxxxxxxxxxxxxxxx>wrote:
> So I want to queue some "invoice" data and propagate it to another
> server.
>
> Of course, like most invoices, it contains one header object and a nested
> table of detail objects. Whoa… Can't queue an object with a nested table.
> Seems like a pretty lame limitation but anyways…
>
> The work around is to change the queue to be grouping by transaction.
> Change the queuing to queue a stream of ANYDATA objects then wrap the header
> and details and queue them, then commit. This works ok, until you try to
> propagate them and I hit a bug in 10.2.0.3 that won't let ANYDATA propagate.
> DBA's don't want to go to 10.2.0.4 where the bug is fixed because .3 is our
> standard.
>
> Ok, so fine, I can't get the queues to propagate. So I'll just Dequeue over
> a dblink. Nope, can't do that either because you can't reference Objects
> over DBLinks.
>
> Then someone suggested you can reference objects on more than one server by
> manually setting the OID. So we try that and still no joy. You can't
> reference an object over a dblink.
>
> But I'm thinking that Propagation does it, somehow. Propagation manages to
> get an object from one server to another. How does it do it?
>
> Any hints besides attempting to convince the DBA's to upgrade would be
> appreciated.
>
>
> >>> This e-mail and any attachments are confidential, may contain legal,
> professional or other privileged information, and are intended solely for the
> addressee. If you are not the intended recipient, do not use the information
> in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP2
>
>
Surrogate key ? ie, your table of "real data" has all the complexity you
like plus a surrogate key, and then you enqueue just the surrogate key.
Dequeue listener picks up the surrogate key, and grabs the real data across
db link. Then your queue payload is trivial (just a scalar number) and you
can have one or many tables of "real" data hanging off it.
--
Connor McDonald
===========================
email: connor_mcdonald@xxxxxxxxx
web: http://www.oracledba.co.uk
"Semper in excremento, sole profundum qui variat"
Other related posts:
- » AQ Nightmare- Brady, Mark
- » Re: AQ Nightmare - Connor McDonald
