Hello All,
First of all, thanks a lot for all your help and have a Great New Year 2020 :)
I would like to bring to your attention the following behavior, which I tested in LiveSQL
( 19.5.0.0.0 ):
create global temporary table gtt ( x int ) on commit delete rows
/
Table created.
insert into gtt values (1)
/
1 row(s) inserted.
select * from gtt
/
X
---
1
declare
pragma autonomous_transaction;
begin
insert into gtt values ( 2 );
commit;
end;
/
Statement processed.
select * from gtt
/
no data found
Remark:
In previous versions, starting from 8i up to 11g, the autonomous transaction above was raising the following error:
ORA-14450: attempt to access a transactional temp table already in use
This behavior is also described in the following thread:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1086033238621 as well as in Tom Kyte's book "Expert One-on-One Oracle".
In version 19c (and possibly also in 18c and 12c), the behavior has changed.
Now the autonomous transaction does NOT raise error any more, but the COMMIT (or ROLLBACK) that ends the autonomous transaction also ends the main transaction, which is quite unusual,
and as a result the uncommitted row inserted by the main transaction was also deleted.
The error ORA-14450 is still documented in version 19c identically to the older versions.
I would like to ask whether this behavior change is deliberate, or it might happen to be a bug that deserves a check ?
Here is a test script to reproduce this case:
https://livesql.oracle.com/apex/livesql/s/jgc0expagu7afd36vhmmdwt6i Thanks a lot in advance & Best Regards,
Iudith Mentzel
Definitely from 12.2 onwards I'm seeing the behaviour you reported.
I'm asking around internally, but I feel it might be a bug.
I'll add more info as it comes to hand.
Update Jan 14:
Bug 30759170 has been logged for this