What's the best practice to avoid collisions in throwaway temp tables?

Question

In SQL Server, I'm used to creating a #temp table for temporary storage in a complex stored procedure. Not wanting to clutter up the system, I DROP the table when done.

However, if I do the same in Oracle:

CREATE GLOBAL TEMPORARY TABLE Temp.RecordsToBeDeleted
    (PK INT NOT NULL PRIMARY KEY, Value VARCHAR2(10) NOT NULL)
-- Populate Temp.RecordsToBeDeleted
-- Use Temp.RecordsToBeDeleted
DROP TABLE Temp.RecordsToBeDeleted

...in one stored procedure, and another stored procedure does something similar and also uses the name Temp.RecordsToBeDeleted, they could collide.

The actual data in the table will not be visible across stored procedures, but the schemas could be different, or I could DROP one while the other is running.

Is there an established best practice for this? I could prefix each table name with the name of the stored procedure, but that's clumsy. I could treat GTTs as first-level objects, created with the rest of the schema rather than being created and dropped as needed, but that would increase maintenance; stored procedures would no longer be wholly self-contained.

Answer 1

The standard approach in Oracle is that global temporary tables are first-level objects. They should be created at install time when you create all your permanent tables. Doing DDL at runtime is very much frowned upon. I'm not sure how this increases maintenance or how it makes stored procedures "no longer wholly self-contained"-- you don't need to do any maintenance on a temporary table and stored procedures presumably already have references to tables so I'm not sure what problem adding a reference to a temporary table creates.

It is unusual, though, in Oracle to use a temporary table in the first place. I've been developing in Oracle for years and I can count the number of temporary tables I've created there on one hand. Perhaps you would be better served by a collection of some type. Perhaps you just need a view that implements whatever logic you're using in SQL Server to populate the temporary table. Perhaps you want a pipelined table function. Perhaps you even want a materialized view. It will depend on why you're creating the temporary table to begin with. Here is another thread that talks about alternatives to temporary tables in Oracle in more detail.