views:

87

answers:

2

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. :)

A: 
  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.

cagcowboy
+4  A: 

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.

Guru
so, with respect to its existence, it behaves just like a normal table --- it's the data that's actually temporary. got it. thanks! :)
Richard Neil Ilagan
That's right. Documentation says "The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table."
Guru