This discussion is archived
1 Reply Latest reply: Jun 18, 2012 4:23 AM by Jocelyn Simard RSS

Is it possible to see DML generated by Applying the LCR?

user398464 Newbie
Currently Being Moderated
We've got a fairly strange situation happening at one of our client sites. Our system includes triggers on the target/replica tables, to track changes for incremental refresh purposes. Been working great (mostly) for years with Streams. However, recently some logic was added to the triggers to NOT record changes if a specific column on the table is being UPDATEd (due to aa source application change), so in the UPDATE section of the trigger, the UPDATING('<COLUMN_NAME'>) function is used to determine if that column is actually being updated or not. THis works because in the source application, this column ONLY evre gets updated during one single process that we DO actually want to ignore. Again, this logic worked great internally as well at almost all client sites. However, at one client, this logic is NOT working, which is leading to the theory that for some reason, when applying the actual LCR to the table, STreams is firing off an UPDATE statement that is updating ALL columns, regardless of whether the values changed or not. Whereas in most situations, the evidence suggests that Streams only puts those columns in the SET clause of the UDPATE statement that are in fact different/changed.

So - this then begs the question: is there some way to "see" the UPDATE statement generated by Streams when applying the LCR? We've been able to look at the actual LCR contents (all the OLD and NEW values) and so can see that this value is in fact, NOT changing. Yet the UPDATING logic in the trigger seems to indicate that it is still being included in the SET clause for some reason...

Have tried adding logic to the trigger itself to look at v$SQL, etc to see the actual UDPATE statement but that doesn't seem to be working. Any other tips for debuging this one?

This is 11.2.0.2 - alternatively: any known Streams bugs w/ this release that we need to look for patches for that might address this?

Thanks for any/all ideas.

Cheers,
Jim

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points