Home » SQL & PL/SQL » SQL & PL/SQL » Advance Queing and tracking transactions
Advance Queing and tracking transactions [message #208416] Sun, 10 December 2006 14:51 Go to next message
Messages: 7
Registered: December 2006
Location: Maribor,Slovenia
Junior Member
Hello again guys,

I would apreciate if anybody could direct me or give me a hint if I am on wrong way. I am still have much to do on idea and study of Advance Queing possibilites in Oracle9i and ORacle 10g.

But let me to make one step maybe to soon and ask if anybody has better idea.

I am trying to track transactions on OLTP system and reroute DML SQL clauses after doing OLTP operation to route in the remote database ( maybe through Advance queing ) where this SQL clauses would go through some agent were data would be transformed
and aditionaly equipped in some PL/sql procedure and than
commit to remote database. I consider Advance Queing mechanism could track and identify original transaction and reroute the copy to the remote database where before Insert ( based on original Inserts, Updates, Deletes ) would aditional transformation would be done.

Thank you in advance for any idea hint or similar experience.

Kind Regards,

Re: Advance Queing and tracking transactions [message #209294 is a reply to message #208416] Thu, 14 December 2006 02:20 Go to previous message
Messages: 7
Registered: December 2006
Location: Maribor,Slovenia
Junior Member
My question on this idea is ?

I have after row ( Insert, Update, Delete ) triggers on original tables. Those triggers would send the message through advanced queueing with following content:
- SQL DML clause with all the values
( from the :new and :old referenced variables )
- User identification,
- Session Identification,
- Transaction Identification
- Id that Identifies group of SQL DML operations
of which this ( INSERT, UPDATE, DELETE ) is part of
( from the beginning to the commit point - I still
have no idea what would happened in savepoint scenario )
- DML clause ( exact listing with values ) or
structured way:
- type of DML clause
( that would demand registered allowed types :
For example Insert into ...select from )
- all values
- tables

2. On the network I have another remote database
where there is subscriber subscribed to those messages
and makes transaction logs in log tables ( indepenedently )
on basis of content of the message.
Commit to this independent database would do on
Transaction_Id values.
I would know it : while Transaction_Id is unchanged all
DMLs in sequence in arrived messages are part of the same
transaction. At the point when Transaction changed I would
do commit. I should also deal with scenarium
when there is no more transactions ( no transaction_Id )
on queue that would mean endless waiting on subscriber side
and no commit for last sequence.

Previous Topic: Transaction Identification
Next Topic: Dynamic columns in crosstab output
Goto Forum:

Current Time: Thu Jul 27 10:28:57 CDT 2017

Total time taken to generate the page: 0.09136 seconds