Re: Oracle Transaction Boundary ..

From: Jim Kennedy <kennedy-downwithspammersfamily_at_attbi.net>
Date: Sun, 15 May 2005 08:32:39 -0700
Message-ID: <n4udndaHAsg69hrfRVn-sA@comcast.com>

"Raghavendra S. Kothamangala" <raghavendra.kothamangala_at_gmail.com> wrote in message news:1116138250.589369.79230_at_o13g2000cwo.googlegroups.com...
> IANAL_VISTA wrote:
> > 1) Do NOT top post, see comments at the bottom!
> >
> ========================================================================
> > "Raghavendra S. Kothamangala" <raghavendra.kothamangala_at_gmail.com>
> wrote in
> > news:1116090157.580677.41120_at_g49g2000cwa.googlegroups.com:
> >
> > > Hi All,
> > >
> > > Thanks for the replies.
> > >
> > > Here is more information about the requirement.
> > >
> > > I have a lot of J2EE applications modifying the oracle database.
> > > The data which is updated in the oracle database has to be
> replicated
> > > in another database (non-oracle).
> > >
> > > Before doing the update, I have to transform the data, and make
> sure
> > > I maintain the transaction integrity.
> > > Constraints:
> > > 1. I cannot change the existing J2EE applications.
> > > 2. Need to rollback change, if one table does not go through.
> > > Transaction Integrity.
> > >
> > > I have ruled out CDC (Change Data Capture in 10G) as we need to use
> > > Views for achieving the same.
> > >
> > > I am exploring using the dbms_transactions package.
> > > We might have to have triggers on all tables in the database which
> can
> > > get modified by J2EE apps.
> > >
> > > When there is modification to the database, we will need to write
> the
> > > data into a separate table common for all the tables in the
> database,
> > > and then take the data from there.
> > >
> > > Thanks again
> > > -Raghu
> > >
> > >
> > > DA Morgan wrote:
> > >> Raghavendra S. Kothamangala wrote:
> > >>
> > >> > Hi,
> > >> >
> > >> > I have a requirement to do custom processing for any new
> > >> > transaction, which happens in a Oracle database.
> > >> >
> > >> > When there is a new transaction in the oracle database, how can
> > >> > I know the following information.
> > >> >
> > >> > 1. The tables involved (which were updated) in the transaction.
> > >> > 2. The sequence of the updates.
> > >> > 3. The source (user) which updated the transaction.
> > >> > 4. The start and commit time.
> > >> >
> > >> > I have to do processing on this data immediately, and update
> > >> > another database.
> > >> >
> > >> > What is the simplest way to achieve this?
> > >> >
> > >> > Suggestions and Pointers are appreciated.
> > >> >
> > >> > Thanks in Advance
> > >> > -Raghu
> > >>
> > >> Without a lot more information, including Oracle version,
> > >> no help is possible beyond wild guesses.
> > >> --
> > >> Daniel A. Morgan
> > >> University of Washington
> > >> damorgan_at_x.washington.edu
> > >> (replace 'x' with 'u' to respond)
> > >
> >
> > Nothing is impossible for those to don't have to do it.
> >
> > The "requirements" are challenging to say the least.
> >
> >
> > > When there is modification to the database, we will need to write
> the
> > > data into a separate table common for all the tables in the
> database,
> > > and then take the data from there.
> >
> > Exactly how do you propose to handle all the various data types which
> could
> > exist?
> >
> > Does J2EE ever issue any DELETE? The above may "work" for INSERT &
> UPDATE,
> > but what gets written to common table for DELETE?
> >
> > Do any type of "LOB" data types exist in the Oracle DB used by J2EE?
> >
> >
> > Only viable solution, IMO, is to modify the J2EE code.
> > But since you said that is not an option, then You're On Your Own
> (YOYO)!

> Hi,

> Thanks for the reply.

> Here are more details, before I proceed with answers to your questions.

> 1. The target database where replication needs to be done can be
> anything.
> We need a generic solution.
> We will need to roll back changes, if an insert or an update fails
> in the target database. None of the tables in the transaction should
> be updated, if one of them fails.

> 2. We have not yet decided on the version of oracle, but we are
> allowed to use 10g.

> 3. Triggers : The idea is to have triggers on insert / delete/ update,
> for each of the tables which can get modified by J2EE.

> 4. The trigger uses the dbms package to get information on the
> transaction and inserts this into a common table. I was thinking of
> putting the entire table data in xml. If this is not possible,
> I can just put the basic information :
> + transaction id
> + table name
> + the order in which the tables were modified,
> + the user who updated the data and
> + time of commit.

> 5. This common table, can be polled by an other application. This will
> pick up the data for each of the transaction id's, and start doing
> the
> custom changes and update the data in the target database.

> > Does J2EE ever issue any DELETE? The above may "work" for
> > INSERT & UPDATE, but what gets written to common table for DELETE?

> Yes, the J2EE applications can issue a delete. For this we are planning
> to have a 'on delete' trigger on each of the tables.
> For auditing purpose we will need to capture the entire table data
> and log it.

> > Do any type of "LOB" data types exist in the Oracle DB used by J2EE?

> Not as of now. But we might think of using in the future.

> Thanks
> -Raghu

>

For auditing why not use Oracle's built in auditing? It would save you a ton of work. It has already been tested. Jim Received on Sun May 15 2005 - 10:32:39 CDT