This content has been marked as final. Show 2 replies
Raghu wrote:On exception, ROLLBACK happens at transaction level.
I am confused about how errors are handled by Oracle.
If I call each procedure individually, first two are successful and third fails with primary key violation error, when I do select it returns 2 records.
I have done rollback after confirming it returns 2 records.
But, when I call all 3 procedures in anonymous block, the block fails with primary key violation error for 3rd procedure and select does not return any data.
Why does Oracle work differently when each procedure is called individually and all are in called in a single script ?
When you run 3 procedures individually, they are 3 seperate transactions. So the exception in the third procedure will effect only its transaction, ie., third transaction.
When you call 3 procedures in an anonymous block, it is a single transaction - Excetion will rollback all the changes done by the three procedures (if there is no intermediate COMMIT).
If you dont want to happen this, put intermediate commmits - Normally that is not a good practice..
Edited by: jeneesh on Apr 19, 2013 9:15 AM
You will be interested to read about cocurrency and consistency - http://docs.oracle.com/cd/B19306_01/server.102/b14220/consist.htm
what is a session?
what is a transaction?
who can see uncommitted DML?
does ERROR result in ROLLBACK?
How do I ask a question on the forums?
SQL and PL/SQL FAQ
post was nice start but does not actually SHOW any results