Global Temporary Table "On commit delete rows" functionality discrepancy.
- by TomatoSandwich
I have a global temporary table. I called my 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. :(
