Unifying Temp table behavior across oracle, mssql

From: <bobdurie_at_gmail.com>
Date: Sun, 22 Jun 2008 07:31:31 -0700 (PDT)
Message-ID: <c56bdce5-0143-4d74-b7c7-abcc0042f130@59g2000hsb.googlegroups.com>


Hi,

I have an java application with a common persistence layer that now has the need for temporary tables. The need has arisen basically because we often need to do IN on large java arrays, and are hitting the bounds on the IN (1, 2, ... 1000s) for both oracle (max 2000 items in an in expression) and mssql (our driver uses a stored procedure, can't have too many parameters) (note, support for 10g+ and ms sql server 2000+).

Anyways, i want to create a common temporary table api - i'm hoping someone can tell me if what i want to do is a BAD idea.

The api needs to support sessions in both auto commit and transactional mode, so for oracle creation of the tables will look like this:

CREATE GLOBAL TEMPORARY TABLE foo (...) ON COMMIT PRESERVE ROWS;

Because we use a global pool of connections, and because i want to free the resources, before the connection goes back in the pool i'll delete the temp table:

TRUNCATE TABLE foo;
DROP TABLE foo;

Does this seem like a bad idea? Is what i'm doing above intense operations? On sql this looks much different, creation:

CREATE TABLE #foo (...);

Dropping the table is simple - only on oracle does the truncation seem necessary:

DROP TABLE foo;

I've noticed a few minor differences, but they don't seem like showstoppers, and seem explainable based on things i've read. For instance, on oracle the TABLE is visible to other sessions after its creation has been committed, but the rows never are. In sql server the temp tables are never visible to other sessions.

Thanks for any help in advance!!

Bob Received on Sun Jun 22 2008 - 09:31:31 CDT

Original text of this message