Re: Oracle Transaction Boundary ..

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)! Received on Sat May 14 2005 - 12:45:41 CDT