Is it possible to create local temporary table..?
We create Global Temporary table by the syntax
Create Global Temporay table ....
This Global Temp table, people at differnet session can work. One session can not see the data of another session.
But I want to create the table and drop it after it is used. It has to be done inside a stored procedure.
Is it possible to create local temporarytable...? If it is possible all the people can create the local temp table at their own seesion with same name and drop the table after it is used..
I don't know what you want to achieve but lets see how exactly a temp table works:
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. The ON COMMIT keywords indicate if the data in the table is transaction-specific (the default) or session-specific:
ON COMMIT DELETE ROWS specifies that the temporary table is transaction specific and Oracle truncates the table (delete all rows) after each commit.
ON COMMIT PRESERVE ROWS specifies that the temporary table is session specific and Oracle truncates the table when you terminate the session.
There is no local temp table as far as I know.
Last edited by Raminder; 07-04-2003 at 12:57 PM.
Re: Is it possible to create local temporary table..?
That might be how you do it in other RDBMS, but what benefit do you get from that? (It sounds to me like a case of "pissing against the wind" as the French would say.)
Originally posted by skdas
But I want to create the table and drop it after it is used
If you really must and all this is happening in the same schema, try creating a table with a name derived from the Audit SID (see SESSIONID in SYS_CONTEXT) - that should be unique.
That's a very "SQLServer approach", to workaround SQL Server's difficulty in optimizing queries with many tables.
In Oracle you don't need to do this -- a quick conversion from the temp table methodology is to embed the temp table query inside an inline view.
Search the forum for examples -- there was one very recently.
Click Here to Expand Forum to Full Width