Global Temporary Table "On commit delete rows" functionality discrepancy.

Question

I have a global temporary table. I shall call him GTT, for that was it's initials. My GTT never hurt anyone, and did everything I bade of it.

I asked my GTT to delete rows on commit. This is a valid function in the creation script of my GTT in oracle. I wanted to be able to have different users see GTT with their own data, and not the data of other people's sessions. 'Delete rows on commit' worked perfectly in our test environment. GTT and I were happy.

But then, I deployed GTT as part of an update to functionality to a client's database. The database doesn't like to play well with GTT. GTT called me up all upset and worried, because it wasn't holding any data any more, and didn't know why.

GTT told me that if someone did:

insert into my_GTT (description) values ('Happy happy joy joy')

he would sing-song back:

1 row inserted. 

However, if the same person tried

select * from my_GTT

GTT didn't know what to do, and he replied

0 rows returned.

GTT was upset that he didn't know what his playmate had inserted.

Please, Stackoverflow, why would GTT forget what was placed into him? He can remember perfectly well at home, but out in the cold harsh world, he just gets so scared. :(

Answer 1

Do you have some setting turned on in your target environment where each statement is auto-committing?

(My experience is in SQL Server, where such is the default, but I understand in Oracle, the default is to keep the transaction open until an explicit commit. Mind, I haven't touched Oracle since ~2000)

Answer 2

I think Damien is right and there is an autocommit. The only other option I can come up with is some sort of connection pool issue (ie the select is being done from a separate session to the insert)