How do Oracle temporary tables exactly work in a stored procedure like this?

Question

Suppose I'm using the following Oracle code in a stored procedure:

CREATE GLOBAL TEMPORARY TABLE temp_table (
    field1 NUMBER,
    field2 NUMBER
)
ON COMMIT DELETE ROWS

This particular stored procedure may be called concurrently by different users at any single moment. As I understand it, the data visible to the user in the temporary table will be private to him or her, and these rows are deleted on a COMMIT.

However, how do the following work with respect to this:

1. Is it safe to call the CREATE statement above every single time the stored procedure is called? Would this result in an error cause there already "exists" a temporary table (possibly) created by a different user (/session)? Or would this be OK, since the server treats them privately anyway?

2. What exactly happens with the ON COMMIT DELETE ROWS? I assume that this only deletes the rows specific to the particular user session, leaving the data by other sessions unharmed, correct?

Any help would be appreciated. :)

Answer 1

1. I'd just leave the table there. No sense in dropping and recreating it all the time. It will cause concurency issues as you say.

2. Yes.

Answer 2

Q1: Is it safe to call the CREATE statement above every single time the stored procedure is called?

The main reason you create a global temporary table (GTT) is to create once (not inside procedure) and use it as private table for a session. It will throw an error if the table already exist.

Q2: What exactly happens with the ON COMMIT DELETE ROWS?

Yes. The data gets deleted once you commit. This happens only for the session you operate.

Check creating GTT and its use.