Our customer has been reporting that some of their screens are coming up empty. However they perform a SQL query and find that there is data there. This was quite a mystery that we could not recreate in development. I took a look at the code involved. Somebody suggested that I inspect the databsae table that gets populated by a stored proedure and used by the application.I found the database table name. But I needed more info. Which schema is this table contained in? I found out it was in the main schema that holds most of our tables. Then I found some strange code that seemed to delete all records out of the table every time somebody logged in. What kind of race condition did they set up here? Before jumping to conclusions, I decided to test out the use of the table with my app.
When I ran the app, I saw data in the application. However I could not find any data in the table when I ran a query. That was strange. This was my own private schema. Nobody else would be working in it. Then another developer thought that the app might be deleting the data. That was a possibility. I decided to remove the app from the picture completely.
I just used some SQL to insert data in the table. I could immediately see the data. However when I tried to query it from another tool (connected in a different session), the data was not there. Aha. I determined the behavior of the table. It turns out it was an Oracle global temporary table. The data was private per session, and removed after session end.
Sometimes you got to test things out for yourself to gain understanding. This was one of those cases. I was not up to speed on how Oracle global temporary tables work. But I knew how to run specific tests to determine what was going on.
