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).
And one might add doesn't not meet the requirements of SarbOx or HIPAA.
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Sun May 15 2005 - 11:07:01 CDT
