font=Arial]Hi,

I would like to know if a rollback is possible in a multi-session table insert.
Consider there are 3 separate sessions.
Each session performs an insert into different tables.
If an error occurs in 3rd session, is it possible to rollback all the 3 inserts?
Could you please help me in this?

Thanks,
Ranjana [/font]

To make it more clear I would explain the entire scenario.

There is a front end application with 3 tabs.
For each tab a separate screen opens up.
All screens contain fund information. eg: Screen1- Fund Overview;Screen2 - Financial; Screen3- Ratings.
The details entered in the front end by the user would be in xml format.
This needs to be inserted into respective Data base tables.There would be separate xml for each screen.
I mentioned it as multi-session as there are individual Web-services call to Data Base procedures for each tab.
If an error occurs in Screen 3 insert, then I need to rollback the records inserted from the first 2 screens.Else the information in the tables related to the fund would be incomplete.
I hope the information provided is clear.


Thanks,
Ranjana

Hi Ranjana,

>> I would like to know if a rollback is possible in a multi-session table insert.

Over the web, rollbacks are very problematic, since sessions can easily disconect, and you can wind-up with a huge mess of half-finished transactions.

I would NOT recommend this approach!

Can you design it to do all of the data collection first, and then only store the rows that need to be added?

*******************************************************

>> If an error occurs in Screen 3 insert, then I need to rollback the records inserted from the first 2 screens.

Instead, don't COMMIT the first two screens until the whole transaction has completed.

Thank you Burleson. I have a query in this regard.
Since each insert occurs in separate sessions, would giving a COMMIT in the third screen be applicable for the first two screens also?


Thanks,
Ranjana

>> Since each insert occurs in separate sessions

Can you change it to keep it in one session?

Just pass-on the insert values, and do the insert once, in screen 3.

"Since each insert occurs in separate sessions, would giving a COMMIT in the third screen be applicable for the first two screens also?"

Think about the ramifications of that statement if that is how Oracle operated.

Hi Burleson,

Thanks for the replies.
Will try out as suggested by you.


Ranjana