How to manage rollbacks in package. 2

[PANTLORD]

Hi there,

I have several stored procedures, updating / inserting into several tables on an ORACLE 8i db in a specific order lets say procedures are 1 to 5.

In order for a complete update of the db I need all to run successfully, if any fail I want to rollback to the very start, that is if procedures 1-3 run successfully but 4 fails I want to take it all the way back to before procedure 1.

Currently they all reside in a package on the server and I intend to call them from a Powerbuilder client.

I have my exception / rollback included in each script but think that this will only roll back the current procedure not all of them, any ideas on how best to manage these rollback requirements ?

Many thanks,
Muly

 

[sem]

ROLLBACK command acts on transaction level, not on program unit level. Thus it rolls back all uncommited changes.

Regards, Dima

http://seminihin.narod.ru

 

[PANTLORD]

Cheers so basically if I ensure the procedure calls are in the correct order and do not do my commit until procedure 5 has successfully processed I should be grand.

Additionally how any ideas how the package / procedure instances are managed on the server with several users having simulaneous access ?

many thanks,
Mully

 

[SantaMufasa]

Mully,

Regardless of how many users invoke a package or procedure, each user session is independent and represents separate transactions that a user can separately rollback or commit. In fact, if I, as one Oracle user, open five different sessions on the same client PC, each of my five sessions represents separate transactions, each with its own ability to commit or rollback its transaction activity.

Does this answer your question?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[PANTLORD]

Thats greats makes sense now, so everything is managed by the transactions, if multiple instances of my client ran on the one client machine each would create its own transaction, these would then be managed individually by ORACLE.

Thanks again,
Mully