1

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.

1 Answer 1

3

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.

2
  • In this particular case, the temp table was to store aggregated data with a defined key, so I could use it in a multi-table UPDATE. Good stuff in that linked answer, thanks! Commented Jan 14, 2015 at 20:14
  • There is NO harm in using GTTs just that you shouldn't perform any DDL on them. Otherwise they just work fine. GTTs in oracle are NOT designed to be created and dropped on demand. If you need that you can generate a unique table name and do a create...drop if you must.
    – Raj
    Commented Jan 15, 2015 at 14:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.