IANAL_VISTA wrote:
> "Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in
> news:n4udndaHAsg69hrfRVn-sA_at_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
> >
> >
> >
>
> AFAIK, Oracle's AUDIT tells which objects got changed or accessed,
> but it does NOT record what values changed within the object(s).
Received on Tue May 17 2005 - 23:42:54 CDT
