Re: Sybase vs Oracle: Temp Tables

In article <35D86C7A.5071_at_ictgroup.com>, jgitomer_at_ictgroup.com says...
> Hi Barry,
>
> Maybe I have been brainwashed by Oracle, but I don't see the need for a
> temporary table. Other than not having to explicitly create and drop a
> table what does it buy me to have temporary tables?
>

The basic problems I have found are

1. create table is DDL and so imposes an implicit commit
2. you need to use DBMS_SQL to issue a create table in PL/SQL
3. you need the explicit drop - in SQL Server (where I have seen temporary tables previously) the temporary table is automatically dropped at end of session.

Temporary tables are very useful as far as I can see for general data manipulation work - the SELECT statement is so powerful, it's a shame not to use it as much as possible.

My particular use was with a data load application. Data is dumped into input tables on the database. From there, PL/SQL procedures are run which scan and validate the data, categorise it, and load it into the main database. I'm doing a lot of procedural work on subsets of the data, which would be easier (and probably more efficient) if I could subset the data to temporary tables prior to validation/loading/logging. Point (a) above here is important, as I want to rollback the whole load process if too many validation errors occur - so I can't do DDL mid-stream.

In this particular case, I have to leave the "temporary" tables permanently defined (and hence I have to maintain the column definitions, etc etc) and simply load/empty them during a load run. This also has concurrency implications, as I can't have two runs using the tables at once (whereas "true" temporary tables are per-session, and so are independent...)

In summary, for a certain class of work (and a certain way of developing applications, I guess) temporary tables would be a very useful feature. I too would like to have them in Oracle.

Temporary tables, and stored procedures/functions which are capable of returning resultsets via an ODBC driver (the latter is rumoured to be coming in 8.0.5), are the two features which I really miss from SQL Server when I'm using Oracle (and both are causing stumbling blocks in a conversion project I'm invlved in...)

Paul Moore. Received on Tue Aug 18 1998 - 10:37:12 CDT