Hi All,
I'm looking for a possible solution to the following problem:

1. My application consists of 2 separate processes (lets call them A
and B), running on 2 separate machines (using CORBA as middleware)
2. Both A & B are using the SAME Oracle-DB instance (probably the same
schema too - but not necessarily)
3. The tables are separate (A has its own tables, and so does B)
4. I want to implement kind of a "distributed" transaction:
a. A will start a transaction, do it's INSERTs, and then "pass" the
transaction to B.
b. B will do it's INSERTs, on the transaction it got from A
c. The COMMIT can be done either by A (after B finishes) or by B.

Does Oracle have any solution for this issue?
(I'm using Oracle 9i, C++, and OTL)

Thanks, merry Christmas and a VERY happy new year...

Allon.

---
Allon Dafner
Formula Telecom Solutions
Visit our website: http://www.fts-soft.com

for the logic you present, I doubt ANY DBMS has such a feature. You
have two programs, separately connected to the DB from two different
machines! They HAVE to be separate transactions.

Your proposed design seems faulty from the outset, but if you really,
really, REALY have to have this kind of feature, you might consider a
third process C that accepts the transaction information from A and B
and at the commit from one of them, build the session and actions to do
the actual transaction in the DB.

Of course then you'll have to handle rollbacks (process B dies before
process A does the commit, WHAT do you do?), data errors (the data A
and B send fails the contraints on the tables being used, now what?),
and other problems (power failure kill the C process, is the
transaction just committed lost or not?).
My strong suggestion is find a new design.
Ed

allond@fts-soft.com wrote:
> Hi All,
> I'm looking for a possible solution to the following problem:
>
> 1. My application consists of 2 separate processes (lets call them A
> and B), running on 2 separate machines (using CORBA as middleware)
> 2. Both A & B are using the SAME Oracle-DB instance (probably the same
> schema too - but not necessarily)
> 3. The tables are separate (A has its own tables, and so does B)
> 4. I want to implement kind of a "distributed" transaction:
> a. A will start a transaction, do it's INSERTs, and then "pass" the
> transaction to B.
> b. B will do it's INSERTs, on the transaction it got from A
> c. The COMMIT can be done either by A (after B finishes) or by B.
>
> Does Oracle have any solution for this issue?
> (I'm using Oracle 9i, C++, and OTL)
>
> Thanks, merry Christmas and a VERY happy new year...
>
> Allon.
>
> ---
> Allon Dafner
> Formula Telecom Solutions
> Visit our website: http://www.fts-soft.com

This has all of the hallmarks of a really bad idea. Can you
explain why it is you think you want to do this?

I can tell you for recent experience that this same thinking went
into a vote tallying application for a very popular American TV
program (that shall remain nameless) and the only way the multitude
of problems were solved was by a complete redesign.

--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)


-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
http://www.newsfeed.com The #1 Newsgroup Service in the World!
-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----

This exact same type of question has appeared before and I have posted
on some possible alternates and their drawbacks. As Daniel and Ed
pointed out: this is not a real good idea; however, we are all working
from just only the barest of details of what you want to do. Perhaps
if you explained what it is you need to accomplish and why you cannot
just send the data from one database to the other then perhaps someone
can provide more helpful information.

Depending on the relationship between the objects in A and B one option
might be to connect to A and perform the update and then via table
triggers in A send the updates to B over a database link You now have
an Oracle managed distributed transaction.

Oracle replication from A to B might be an option.
HTH -- Mark D Powell --

Mark D Powell wrote:
> This exact same type of question has appeared before and I have posted
> on some possible alternates and their drawbacks. As Daniel and Ed
> pointed out: this is not a real good idea; however, we are all working
> from just only the barest of details of what you want to do. Perhaps
> if you explained what it is you need to accomplish and why you cannot
> just send the data from one database to the other then perhaps someone
> can provide more helpful information.
>
> Depending on the relationship between the objects in A and B one option
> might be to connect to A and perform the update and then via table
> triggers in A send the updates to B over a database link You now have
> an Oracle managed distributed transaction.
>
> Oracle replication from A to B might be an option.
> HTH -- Mark D Powell --

Exactly. I'm still not convinced the OP means database rather than
schema given the skill set posted.

--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)


-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
http://www.newsfeed.com The #1 Newsgroup Service in the World!
-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----

On 23 Dec 2004, allond@fts-soft.com wrote:
> Hi All,
> I'm looking for a possible solution to the following problem:
>
> 1. My application consists of 2 separate processes (lets call them A
> and B), running on 2 separate machines (using CORBA as middleware)
> 2. Both A & B are using the SAME Oracle-DB instance (probably the same
> schema too - but not necessarily)
> 3. The tables are separate (A has its own tables, and so does B)
> 4. I want to implement kind of a "distributed" transaction:
> a. A will start a transaction, do it's INSERTs, and then "pass" the
> transaction to B.
> b. B will do it's INSERTs, on the transaction it got from A
> c. The COMMIT can be done either by A (after B finishes) or by B.
>
> Does Oracle have any solution for this issue?
> (I'm using Oracle 9i, C++, and OTL)

No server will guarantee an outside process transactional integrity
across processes. Oracle can handle 2 phase commits, but Oracle
must be in control of it. EJBs can handle 2 phase commits but the
container must be in control. What you can't do is ask Oracle to
guarantee the transactional integrity across two unrelated processes.

--
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.

I know this is a bad idea. Believe me I know (
I'll try to explain the nature of my problem a bit more:

What I have are 2 separate *existing* applications. Both are working,
valid products.
What I was asked to do is integrate the 2 apps, with minimal impact on
any of them (and of course we need it now, has to be robust, etc...)

Application 'A' is GUI-oriented, and application 'B' is more of a
back-office nature.
What I want to achieve is that when 'A' creates a new entity, it will
call 'B' to create the needed entity/ies, and then one commit will be
done - to ensure that either both 'A' and 'B' succeded, or nobody did.
Hope this clarifies the problem a bit...

Thanks,

Allon.

Oracle replication is not a good solution since 'A' and 'B' are
different applications, with completely different tables and
data-structures.
I can make them run on the same instance, and even the same schema if
so needed.
Please see some more details in my posting below.

Thanks,

Allon.

You wrote "Oracle can handle 2 phase commits, but Oracle
must be in control of it"
Can you give me some more details?
How can I use Oracle to handle this?

Thanks,

Allon.

allond@fts-soft.com wrote:

> I know this is a bad idea. Believe me I know (
> I'll try to explain the nature of my problem a bit more:
>
> What I have are 2 separate *existing* applications. Both are working,
> valid products.
> What I was asked to do is integrate the 2 apps, with minimal impact on
> any of them (and of course we need it now, has to be robust, etc...)
>
> Application 'A' is GUI-oriented, and application 'B' is more of a
> back-office nature.
> What I want to achieve is that when 'A' creates a new entity, it will
> call 'B' to create the needed entity/ies, and then one commit will be
> done - to ensure that either both 'A' and 'B' succeded, or nobody did.
> Hope this clarifies the problem a bit...
>
> Thanks,
>
> Allon.

I think the best solution, well other than firing the person in
management that purchased the original software without considering
integration issues, would be to write a third application that
interacts with A and B.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)