Creating a unique database table name

[newora]

Hi There,

I wonder if someone could please help me with a problem that I seem to be unable to get clear in my mind.

I have an application that needs to create a uniquely named database table (from an existing know table that I am using as a 'template'). I thus thought that there may be a function in the Oracle database system that will return me a unique name that I can use to create the database table with.

The application runs on a cluster server and needs to create a unique database table for a report, as it is possible for about 6 or 7 users to proeduce the report at the same time.

I am using Crystal reports and I think that I can then pass the name of the unique database table to the report using the datafiles property, but I am completely lost as to how to create a unique table in the database and get its name. I thought about using the username that they log onto the server with , but they all use the same login name. I then thought about the getting the curent time and adding that onto a base table name, but I suppose that could still create a duplicate table name.

Any help suggestions would be much appreciated.
Thanks Newora

 

[LKBrwnDBA]

Instead of trying to create unique table names for each user, look into Global Temporary Tables which may solve your problem.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Newora,

How does the application uniquely identify which (of the possibly many) user sessions for which it is creating the table?

In the virtual view, "v$session", each of the currently connected sessions has a variety of uniquely identifying information, including the unique contents of the "SERIAL#" column, even if the application users connect to the application as the same Oracle user.

Let us know if this information is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Beilstwh]

I would use a GTT, but if you must have a unique table then the following should do it

declare
new_file  varchar2(31);
string    varchar2(100);
begin
  SELECT 'JUNK'||USERENV('SESSIONID')
  INTO NEW_FILE
  FROM DUAL;

  execute immediate 'create table '||NEW_FILE'|| AS SELECT * FROM JUNK WHERE 1=2';
END;

Bill
Oracle DBA/Developer
New York State, USA

 

[newora]

Great, thanks very much for the replies - I think I will try each one out to see how it works.

I did read about GTT but did not think that they would be unique for session - more reading to do for me I think.

Thanks again for the quick replies.