| Local Temporary Tables / Oracle vesrion of DB2 temporary table in a session [message #214111] |
Mon, 15 January 2007 00:37  |
gaurav_knowledge
Messages: 3
Registered: January 2007
|
Junior Member |
|
|
hi,
I am working on the migration of an application from DB2 to Oracle. The Db2 application uses statements like these:
declare global temproary table session.tableName ...;
This creates a temporary table named tableName in a session. And another user can login into another session and can create a table with the same name, and can drop it, or can do any other operation. It means these tables are local to a session.
However the correponding replacement in Oracle doesn't allow to create a session specific temporary table:
create global temporary table schema.tableName ...;
Note that here you have to create table in a schema, and the implicit session schema for session tables is not here.
Now if another user logs into another session and tries to create a table with same name, it gives an SQL error that the table already exists.
I can't use something like
create table if it not exists
because the next user in another session can try to create the table with same name but different schema (different columns & datatypes).So my problem is I have to create temporary tables with same name, but different schema, from different sessions.
Can anyone suggest, how can I create a temporary table, local to a session in Oracle. The definition of which should only be available to the user connected to this session. I searched on internet and found that Oracle 7 had a concept of local temproary table. Is there a replacement for this in Oracle 10g?
thanks,
Gaurav
|
|
|
|
| Re: Local Temporary Tables / Oracle vesrion of DB2 temporary table in a session [message #214115 is a reply to message #214111] |
Mon, 15 January 2007 01:01  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The idea with Oracle is to create it once and leave it there. The data is not permanent so it doesn't take up space. You should not have CREATE statements in your application code.
The closest equivalent to LOCAL temp tables in Oracle is PL/SQL collections - see the PL/SQL manual. If the collection is based on a TYPE that is declared on the database then you can access it with SQL, otherwise you have to use PL/SQL statements. Although if you are going to create a collection TYPE on the database, you might as well just create the GTT.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
