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.
- 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.
- We have not yet decided on the version of oracle, but we are
allowed to use 10g.
- Triggers : The idea is to have triggers on insert / delete/ update,
for each of the tables which can get modified by J2EE.
- 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.
- 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
Received on Sun May 15 2005 - 01:24:10 CDT
