Re: Creating Temporary Tables in Oracle

From: Philippe Verdy <verdy_p_at_wanadoo.fr>
Date: Thu, 29 Oct 1998 15:58:12 +0100
Message-ID: <719vr2$25c$1@minus.oleane.net>

sumanm_at_my-dejanews.com wrote in message = <7188li$ogo$1_at_nnrp1.dejanews.com>...
>How can I create temporary tables in Oracle ?
>I know that SQL Server supports temporary tables.
>Basically, I need to create a temporary table containing
>a subset of the data from another table. Then I want
>to update some rows in that temporary table,
>use it and later drop it.
>I don't want anyone else who has logged on using the
>same userid/pwd to see my temporary table.
>How do I achieve that ?

Jerry Gitomer a écrit dans le message = <719p9j$42s$1_at_client3.news.psi.net>...
>When you create a table in an Oracle session it is not visible to any
>other user, including other sessions for the same user, until a commit =
is
>done. If you can avoid doing an implicit commit until you are ready to =
drop
>the table (and of course don't do an explicit commit) you will not have =
a
>problem.
>If a commit is done there is no way of preventing anyone logged in with =
the
>same username from finding out the name of the table and, once they =
know the
>name, insert, delete, update, or even drop the table as well as select =
ing
>from it.
>Picture the following scenario. You create a table giving it an =
esoteric
>name unknown to your co-workers. Joe the practical joker logs in using =
the
>same username and does a:
> SELECT TABLE_NAME FROM USER_TABLES;
You're wrong in some parts !

CREATE TABLE is not a DML statement. And as such, it can't be used = within a transaction or within a PL/SQL block which must be compilable = with all its objects resolved to already existing objects before it can = be executed. Even when you issue dynamic SQL using the DMBS_SQL package = in your PL/SQL block or procedure, the table is created in another = server session which is implicitly committed in that extra session, but = this does not alter the transaction in the session which can't reference = the newly created table.

Any CREATE or DROP or ALTER statement is accepted provided it is = executed alone as a single query batch. It has the side effect of = forcing a COMMIT within the current session before creating the table. = Then the table is created, and the session's transaction is implicitly = committed. Further requests in the session will use the new schema = catalog and will be parsed and compiled according to the new schema.

There is no strict equivalent to Sybase or MS-SQL temporary tables. = Oracle internally uses the temporary tablespace for its internal storage = of temporary result-sets created by partial sorts (GROUP BY, UNION, = DISTINCT, and merge-joins) and full sorts (ORDER BY).

Within a transaction, you can simulate temporary result-sets using = opened Oracle cursors. While a cursor is opened, the associated result = set is kept, and you can even delete the whole content of the associated = tables: the cursor will still fetch the data set which existed at the = time of cursor opening.

But if you need to create to store temporary data and then make some = queries with joins on that data, you must create permanent tables for = your temporary data. Provided that your transaction first issues a = "DELETE FROM temptable" at the beginning (which acquires the exclusive =

locks on the whole table contents which should be empty), and at end =
(which leaves the table with no more data if you need to COMMIT your =
work in the PL/SQL block or procedure), the inserted data within the =

block will be only seen by the current transaction, and not by = concurrent transactions.

After a COMMIT or ROLLBACK your permanent table should be kept always = empty for other users, and you won't need to add any session id column = in your table and in the WHERE clause of your queries.

The issue is that such useage cannot reproduce exactly what a Sybase or = MS-SQL temporary table does: if you needed to create temporary tables = within a recursive procedure (for example that traverses a hierarchy = tree with parent-child relations), each recursive call will participate = in the same transaction, so parent data-sets will be deleted by the = recursive child call which uses the same permanent object. The only way = to solve it cleanly is to rewrite your procedure so that it won't use = recursion. This often involves using loops instead, and simulating the = recursion using a stack stored in a temporary space (i.e. you need to = create a permanent table for that stack and use it to push and pop one = row at each loop containing the data required by the pseudo-recursion).

The other issue is that Sybase temporary objects have names which do not = collapse between concurrent procedures and batches. But you Oracle = permanent table does not have such functionality. So you must make sure = that tables used for temporary tables won't have identical names. One = way of achieving it is to use a reserved prefix "tmp_" for all these = tables, and to include the caller name within the table name. For = example a procedure named "raise_salary" could use a pseudo-temporary = table named "tmp_raise_salary", or "tmp_raise_salary_2", etc... That way = you can isolate all uses of tables which can participate in concurrent = transactions or within the same transaction. Using a reserved prefix = also facilitates the administration of storage used by these tables = (which should always be kept empty outside of any transaction for = maximum performance), so that the administrator can run a night batch = which will truncate all these tables. Received on Thu Oct 29 1998 - 08:58:12 CST