devjnr_at_gmail.com wrote:
> I noticed that a commit inside a procedure, commit also all changes
> outside its begin...end block.
>
> Is it possible? Why?
>
> begin
>
> insert into test values ('test');
> aaa_proc1; -- in this procedure at the end there is commit
> instruction.
>
> end;
>
> This anonymous block cause 'test' value to be inserted.
>
> Is it correct? I think (proc) commit should affect only its begin end
> bloc....
You are confusing save points with transactions.
Could it be you have an MS SQL Server background?
In SQL Server a nested transaction isn't really committed until the
outermost transaction commits. (it's a stack kind of thing)
In Oracle (and DB2 FWIW) transactions are always global.
If you want to open a new nested scope to be rolled back individually
you use a save point.
An autonomous transaction is an entirely different animal. An autonomous
transaction is entirely separated from the transaction that started.
It's as if the transaction had opened a separate connection to the
database.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/
Received on Mon Aug 28 2006 - 07:31:39 CDT
