Commit question
If I have a stored procedure A that inserts record in table A and calls
stored procedure B that inserts record in table B, does Oracle consider
this a logical record? Meaning, if I rollback in stored procedure
A, will the insert in table B be also rolled back? My Commit in A
also commits my B?
Ans1:
A commit ends a transaction/logical unit of work and commits any change
after the previous commit or rollback.
So if your procedure A is like this
procedure a is
begin
insert into blablba;
b;
rollback
the changes in b will be rollback also. If you replace this by a commit
both the changes in a and b will be committed.
If you don't want that you can setup a savepoint in procedure b and
when anything goes wrong in B rollback to that savepoint.
Ans2:
You need to declare a transaction for that.
You can use :
DBMS_TRANSACTION.READ_WRITE;
at the begining
DBMS_TRANSACTION.COMMIT; or DBMS_TRANSACTION.ROLLBACK; at the end
Ans3:
In other databases you need to 'begin work' but not in Oracle.
A transaction is
implicitly started with the first statement and is not ended until
you simply
"commit" or "rollback".
No need for the dbms_transaction package to do this. It just happens.