It's been a while since my last post; I've spent that last few weeks buried in the middle of a large application that we're porting from SQL Server to Oracle (purely for the customer's needs, not due to any issue with SQL Server).
It's been hard work with a major amount of refactoring, but nothing particularly blog-worthy. Until today, that is :)
I've spent the last 4 hours chasing down a strange "Object no longer exists" exception which was getting thrown at various points when I tried to execute particular stored procs. The exception wasn't particularly helpful in diagnosing the issue, and since I'm a bit of an Oracle noob, it's taken me a while. However, I'm sure that there are other Oracle noobs out there, so hopefully this will help someone.
Doing googles on the error throws up a number of nasty things about corrupt pages, bug patches etc. I was pretty sure from the start that it was unlikely to be any of those. Since the debugger wasn't being particularly helpful, I resorted to the time-honoured technique of commenting out large swathes of code until things worked, and then gradually (in true geeky binary-chop fashion) re-introducing it.
This finally nailed it down to an "open c for select..." statement that was returning a cursor over a result set out to the caller. Nothing unusual there, most of my stored procs are doing similar things. However, this one included a join to a global temporary table. Remove the join, everything works. Re-introduce the join and the fireworks begin again.
That was it. Problem solved. In Oracle, you can create temporary tables with two options, "on commit delete rows" and "on commit preserve rows". The first of these effectively dumps the temp table as soon as a commit occurs, whereas the second holds on to the data until the session ends. My temporary tables were created with the first option, meaning that as soon as the transaction they were embedded in committed, they got dumped. My C# code then tried to read from the cursor that was over this dead table, and we get the lovely ora-08103 exception.
The fix? For me, nice and easy. Just change to the "on commit preserve rows" option. Job done, and I'm off to bed.