Reply from BLAXMIKANTA on Jun 23 at 4:32 PM Million thanks to iudithm. It's a nice explanation.
| | | ---------------Original Message---------------
From: iudith mentzel
Sent: Sunday, June 03, 2012 10:50 AM
Subject: Rollback in Oracle Exception
Hello,
In general, it is a BAD and not recommended practice to use COMMIT or ROLLBACK
in a stored procedure !
There exist some exceptions to this, but those are very specific cases, like for example procedures that are designed for performing a complete task , independent from any other task and are usually run from a batch job.
Another exception are procedures declared as AUTONOMOUS TRANSACTION,
which execute as a separate (complete) transaction, therefore they should end
their transaction by performing a COMMIT or ROLLBACK.
In a procedure that is called from a CLIENT application, with parameters passed to it,
a.s.o, it is the CALLING APPLICATION that should control the transaction, that is,
the calling application itself should issue COMMIT or ROLLBACK as required .
The COMMIT and ROLLBACK should be driven entirely by the application business logic, because only the application "knows" when a transaction should start and where it should end.
From a strictly technical point of view:
If you call a procedure that performs DML as part of the ongoing transaction,
then, when the procedure ends, it does NOT perform automatically neither a COMMIT
nor a ROLLBACK.
If such a procedure ends with an unhandled exception (either raised by you or automatically by Oracle following an error), then the outcome depends from how the
client application handles this situation.
For example, if you call a procedure from SQL*PLUS ( using EXEC or an anonymous block ), then, if the procedure ends with an error, then SQL*PLUS will automatically roll back all the DML changes performed by that procedure and that were not committed
by the procedure itself.
As explained above, it is usually recommended NOT to commit from the procedure.
In case that an exception is raised, this will propagate to the client, which should
handle it appropriately, like: issue an error message to the user, perform a rollback
if necessary, a.s.o.
Note the difference between a STORED procedure and client-side PL/SQL !
While in a stored procedure it is recommended NOT to use COMMIT / ROLLBACK,
the client-side PL/SQL , which controls the transaction, should be the one that issues
COMMIT / ROLLBACK statements.
Hope this information could help.
Best Regards,
Iudith |
| Reply to this email to post your response. __.____._ |   _.____.__ |
No comments:
Post a Comment