At work we have a large system coded mainly in the C++ programming language. The back end is an Oracle 10g database. Users have numerous requirements for configurable reports. We implement these using Oracle Reports. Frequently the application will create temporary tables in the user’s schema. The table names are passed to the report. This allows long running reports to essentially work on snapshots of the data. It also simplifies some of the data gathering performed in the actual reports.
Each time a new reported is selected, the application shall typically delete the prior temporary tables. The application then recreates the temporary tables based on user selections and current data in the database. This usually works fine since the same users normally run the same reports over and over. The only tricky part is that sometimes a user will choose a report for the first time. And in that scenario, there is no existing temporary table to delete.
My instinct for this scenario is to first detect whether the table exists or not. This can be a query from the USER_TABLES view. Once I find that the count is not zero, the code executes SQL to drop the old table. I was surprised to find another technique used in some code I recently debugged. It always attempted to drop the old table even if it did not exist. It preceded this code with a EXEC SQL WHENEVER SQLERROR DO CONTINUE.
Logically the ignore errors method works just as well as the count with optional drop of table. I started to wonder which way was better though. One would think the ignore errors method was better for the normal case. Only one SQL statement was issued. I would imagine this to run faster. But maybe there is heavy penalty for trying to drop a table that does not exist. In the end I left the code alone to ignore errors and always try to drop the table which might not even exist.
This whole discovery got started by a tester finding a scenario where the DROP failed and then a following CREATE TABLE failing as well. But that story is a subject for a future post.
