"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 Sun May 15 2005 - 10:43:18 CDT
