Unifying Temp table behavior across oracle, mssql

#1

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

#2

"Marcin Wróblewski" <m_wroblewski (AT) gazeta (DOT) pl> wrote

Quote:

bobdurie (AT) gmail (DOT) com pisze:
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

Does this seem like a bad idea? Is what i'm doing above intense
operations?

I don't know MSSQL but on ORACLE it's a very bad idea.

I would simply

1) DELETE FROM FOO;
2) do sth. with foo

without dropping and creating foo over and over. This table should be
created once.

In fact I would never consider using auto commit.

CREATE GLOBAL TEMPORARY TABLE foo (...) ON COMMIT DELETE ROWS

and then
1) do sth. with foo;
2) COMMIT;
I have to agree with Marcin.

1. Very poor application design to use auto commit.
2. Sql= stands for Structured Query Language not an MS product.
3. You are hitting the problem of a db agnostic where not all vendor's
products support things in the same manner. On Oracle I might not even
bother with a global temporary table but pass in a collection and join that
with the data. (assuming the collection wasn't so huge that it caused a
memory problem. eg select .. from aTable a where a.somcolumn in (select
collection.column_value from table(cast(mycollestion as t_mycollection)));
assuming a simple collection like a table of numbers. A more compex
collection would need the column name.
Jim

#3

"Marcin Wróblewski" <m_wroblewski (AT) gazeta (DOT) pl> wrote

Quote:

bobdurie (AT) gmail (DOT) com pisze:
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

Does this seem like a bad idea? Is what i'm doing above intense
operations?

I don't know MSSQL but on ORACLE it's a very bad idea.

I would simply

1) DELETE FROM FOO;
2) do sth. with foo

without dropping and creating foo over and over. This table should be
created once.

In fact I would never consider using auto commit.

CREATE GLOBAL TEMPORARY TABLE foo (...) ON COMMIT DELETE ROWS

and then
1) do sth. with foo;
2) COMMIT;
I have to agree with Marcin.

1. Very poor application design to use auto commit.
2. Sql= stands for Structured Query Language not an MS product.
3. You are hitting the problem of a db agnostic where not all vendor's
products support things in the same manner. On Oracle I might not even
bother with a global temporary table but pass in a collection and join that
with the data. (assuming the collection wasn't so huge that it caused a
memory problem. eg select .. from aTable a where a.somcolumn in (select
collection.column_value from table(cast(mycollestion as t_mycollection)));
assuming a simple collection like a table of numbers. A more compex
collection would need the column name.
Jim

#4

"Marcin Wróblewski" <m_wroblewski (AT) gazeta (DOT) pl> wrote

Quote:

bobdurie (AT) gmail (DOT) com pisze:
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

Does this seem like a bad idea? Is what i'm doing above intense
operations?

I don't know MSSQL but on ORACLE it's a very bad idea.

I would simply

1) DELETE FROM FOO;
2) do sth. with foo

without dropping and creating foo over and over. This table should be
created once.

In fact I would never consider using auto commit.

CREATE GLOBAL TEMPORARY TABLE foo (...) ON COMMIT DELETE ROWS

and then
1) do sth. with foo;
2) COMMIT;
I have to agree with Marcin.

1. Very poor application design to use auto commit.
2. Sql= stands for Structured Query Language not an MS product.
3. You are hitting the problem of a db agnostic where not all vendor's
products support things in the same manner. On Oracle I might not even
bother with a global temporary table but pass in a collection and join that
with the data. (assuming the collection wasn't so huge that it caused a
memory problem. eg select .. from aTable a where a.somcolumn in (select
collection.column_value from table(cast(mycollestion as t_mycollection)));
assuming a simple collection like a table of numbers. A more compex
collection would need the column name.
Jim

#5

"Marcin Wróblewski" <m_wroblewski (AT) gazeta (DOT) pl> wrote

Quote:

bobdurie (AT) gmail (DOT) com pisze:
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

Does this seem like a bad idea? Is what i'm doing above intense
operations?

I don't know MSSQL but on ORACLE it's a very bad idea.

I would simply

1) DELETE FROM FOO;
2) do sth. with foo

without dropping and creating foo over and over. This table should be
created once.

In fact I would never consider using auto commit.

CREATE GLOBAL TEMPORARY TABLE foo (...) ON COMMIT DELETE ROWS

and then
1) do sth. with foo;
2) COMMIT;
I have to agree with Marcin.

1. Very poor application design to use auto commit.
2. Sql= stands for Structured Query Language not an MS product.
3. You are hitting the problem of a db agnostic where not all vendor's
products support things in the same manner. On Oracle I might not even
bother with a global temporary table but pass in a collection and join that
with the data. (assuming the collection wasn't so huge that it caused a
memory problem. eg select .. from aTable a where a.somcolumn in (select
collection.column_value from table(cast(mycollestion as t_mycollection)));
assuming a simple collection like a table of numbers. A more compex
collection would need the column name.
Jim

#6

"Marcin Wróblewski" <m_wroblewski (AT) gazeta (DOT) pl> wrote

Quote:

bobdurie (AT) gmail (DOT) com pisze:
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

Does this seem like a bad idea? Is what i'm doing above intense
operations?

I don't know MSSQL but on ORACLE it's a very bad idea.

I would simply

1) DELETE FROM FOO;
2) do sth. with foo

without dropping and creating foo over and over. This table should be
created once.

In fact I would never consider using auto commit.

CREATE GLOBAL TEMPORARY TABLE foo (...) ON COMMIT DELETE ROWS

and then
1) do sth. with foo;
2) COMMIT;
I have to agree with Marcin.

1. Very poor application design to use auto commit.
2. Sql= stands for Structured Query Language not an MS product.
3. You are hitting the problem of a db agnostic where not all vendor's
products support things in the same manner. On Oracle I might not even
bother with a global temporary table but pass in a collection and join that
with the data. (assuming the collection wasn't so huge that it caused a
memory problem. eg select .. from aTable a where a.somcolumn in (select
collection.column_value from table(cast(mycollestion as t_mycollection)));
assuming a simple collection like a table of numbers. A more compex
collection would need the column name.
Jim

#7

"Marcin Wróblewski" <m_wroblewski (AT) gazeta (DOT) pl> wrote

Quote:

bobdurie (AT) gmail (DOT) com pisze:
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

Does this seem like a bad idea? Is what i'm doing above intense
operations?

I don't know MSSQL but on ORACLE it's a very bad idea.

I would simply

1) DELETE FROM FOO;
2) do sth. with foo

without dropping and creating foo over and over. This table should be
created once.

In fact I would never consider using auto commit.

CREATE GLOBAL TEMPORARY TABLE foo (...) ON COMMIT DELETE ROWS

and then
1) do sth. with foo;
2) COMMIT;
I have to agree with Marcin.

1. Very poor application design to use auto commit.
2. Sql= stands for Structured Query Language not an MS product.
3. You are hitting the problem of a db agnostic where not all vendor's
products support things in the same manner. On Oracle I might not even
bother with a global temporary table but pass in a collection and join that
with the data. (assuming the collection wasn't so huge that it caused a
memory problem. eg select .. from aTable a where a.somcolumn in (select
collection.column_value from table(cast(mycollestion as t_mycollection)));
assuming a simple collection like a table of numbers. A more compex
collection would need the column name.
Jim

#8

"Marcin Wróblewski" <m_wroblewski (AT) gazeta (DOT) pl> wrote

Quote:

bobdurie (AT) gmail (DOT) com pisze:
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

Does this seem like a bad idea? Is what i'm doing above intense
operations?

I don't know MSSQL but on ORACLE it's a very bad idea.

I would simply

1) DELETE FROM FOO;
2) do sth. with foo

without dropping and creating foo over and over. This table should be
created once.

In fact I would never consider using auto commit.

CREATE GLOBAL TEMPORARY TABLE foo (...) ON COMMIT DELETE ROWS

and then
1) do sth. with foo;
2) COMMIT;
I have to agree with Marcin.

1. Very poor application design to use auto commit.
2. Sql= stands for Structured Query Language not an MS product.
3. You are hitting the problem of a db agnostic where not all vendor's
products support things in the same manner. On Oracle I might not even
bother with a global temporary table but pass in a collection and join that
with the data. (assuming the collection wasn't so huge that it caused a
memory problem. eg select .. from aTable a where a.somcolumn in (select
collection.column_value from table(cast(mycollestion as t_mycollection)));
assuming a simple collection like a table of numbers. A more compex
collection would need the column name.
Jim

#9

On Jun 22, 11:34 am, "gym dot scuba dot kennedy at gmail"
<kenned... (AT) verizon (DOT) net> wrote:

Quote:

"Marcin Wróblewski" <m_wroblew... (AT) gazeta (DOT) pl> wrote in message

news:g3lpti$j6g$1 (AT) inews (DOT) gazeta.pl...

bobdu... (AT) gmail (DOT) com pisze:
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

Does this seem like a bad idea? Is what i'm doing above intense
operations?

I don't know MSSQL but on ORACLE it's a very bad idea.

I would simply

1) DELETE FROM FOO;
2) do sth. with foo

without dropping and creating foo over and over. This table should be
created once.

In fact I would never consider using auto commit.

CREATE GLOBAL TEMPORARY TABLE foo (...) ON COMMIT DELETE ROWS

and then
1) do sth. with foo;
2) COMMIT;

I have to agree with Marcin.
1. Very poor application design to use auto commit.
2. Sql= stands for Structured Query Language not an MS product.
3. You are hitting the problem of a db agnostic where not all vendor's
products support things in the same manner. On Oracle I might not even
bother with a global temporary table but pass in a collection and join that
with the data. (assuming the collection wasn't so huge that it caused a
memory problem. eg select .. from aTable a where a.somcolumn in (select
collection.column_value from table(cast(mycollestion as t_mycollection)));
assuming a simple collection like a table of numbers. A more compex
collection would need the column name.
Jim

Wow, thanks for the quick replies!!!

In response to the first response, i see your point - creating the
table over and over is a BAD idea. Of course, this will then lead me
to the annoying problem of managing these tables, something i wanted
to avoid

Most of the time the tables are simply a single column of
type int or varchar, so i could conceivably just create them and have
them around. If the data across sessions doesn't collide it will
never be a problem, and i rarely/never need to create more than one at
once.

For the second comment:
1) Poor app design to use auto commit - this is a possibility. We use
auto commit when have a lot of read only / non-automic operations to
make on the database. We fetch a connection, set it into auto commit,
then access all the tables we need to in whatever order we like.
Because we have to support SQL Server, we have to be careful about
doing such things with auto-commit OFF because of possible deadlocking
issues. I know the concern doesn't exist on the oracle, but it would
lead to far more issues maintaining 2 code bases. I suppose the
alternative would be to simply NEVER use auto-commit on oracle, which
is something i may consider doing... if theres no risk on oracle, it
wouldn't create an issue. Can you tell me why exactly using auto-
commit is indicative of bad app design? Based on this, i may simply
be able to make this change.
2) Sorry, MSSQL = Microsoft SQL Server, my bad

3) I didn't even know collections existed!!! I'll read up on them
now, although depending on how general and supported the jdbc api's
are, this may put me into a bad position in terms of database
agnosticism, but of course this is the cross i bear.

Thank you so much for your replies!!!

#10

On Jun 22, 11:34 am, "gym dot scuba dot kennedy at gmail"
<kenned... (AT) verizon (DOT) net> wrote:

Quote:

"Marcin Wróblewski" <m_wroblew... (AT) gazeta (DOT) pl> wrote in message

news:g3lpti$j6g$1 (AT) inews (DOT) gazeta.pl...

bobdu... (AT) gmail (DOT) com pisze:
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

Does this seem like a bad idea? Is what i'm doing above intense
operations?

I don't know MSSQL but on ORACLE it's a very bad idea.

I would simply

1) DELETE FROM FOO;
2) do sth. with foo

without dropping and creating foo over and over. This table should be
created once.

In fact I would never consider using auto commit.

CREATE GLOBAL TEMPORARY TABLE foo (...) ON COMMIT DELETE ROWS

and then
1) do sth. with foo;
2) COMMIT;

I have to agree with Marcin.
1. Very poor application design to use auto commit.
2. Sql= stands for Structured Query Language not an MS product.
3. You are hitting the problem of a db agnostic where not all vendor's
products support things in the same manner. On Oracle I might not even
bother with a global temporary table but pass in a collection and join that
with the data. (assuming the collection wasn't so huge that it caused a
memory problem. eg select .. from aTable a where a.somcolumn in (select
collection.column_value from table(cast(mycollestion as t_mycollection)));
assuming a simple collection like a table of numbers. A more compex
collection would need the column name.
Jim

Wow, thanks for the quick replies!!!

In response to the first response, i see your point - creating the
table over and over is a BAD idea. Of course, this will then lead me
to the annoying problem of managing these tables, something i wanted
to avoid

Most of the time the tables are simply a single column of
type int or varchar, so i could conceivably just create them and have
them around. If the data across sessions doesn't collide it will
never be a problem, and i rarely/never need to create more than one at
once.

For the second comment:
1) Poor app design to use auto commit - this is a possibility. We use
auto commit when have a lot of read only / non-automic operations to
make on the database. We fetch a connection, set it into auto commit,
then access all the tables we need to in whatever order we like.
Because we have to support SQL Server, we have to be careful about
doing such things with auto-commit OFF because of possible deadlocking
issues. I know the concern doesn't exist on the oracle, but it would
lead to far more issues maintaining 2 code bases. I suppose the
alternative would be to simply NEVER use auto-commit on oracle, which
is something i may consider doing... if theres no risk on oracle, it
wouldn't create an issue. Can you tell me why exactly using auto-
commit is indicative of bad app design? Based on this, i may simply
be able to make this change.
2) Sorry, MSSQL = Microsoft SQL Server, my bad

3) I didn't even know collections existed!!! I'll read up on them
now, although depending on how general and supported the jdbc api's
are, this may put me into a bad position in terms of database
agnosticism, but of course this is the cross i bear.

Thank you so much for your replies!!!