temp table for each user ????

[request]

Is there a way to create a temp table in ORacle 8 that would be local to each user.

I have a crystal report in which I am using the stored procedure. In my stored procedure, I am deleting from a temp table and repopulating it based on the parameter values entered by the user. Now, if multiple users are accessing the same report at the same time, they will be deleting / repopulating the same temp table at the same time. How can I create a temp table for each user from where they can delete / repopulate so that they do not interfere wach other's work?

please help. thanks.

 

[Turkbear]

Hi,
If the database logon is different for each user running the Crystal Report( based on your design, or by requiring a login at run time) then the TEMP table will be created in their own schema ( assuming they have rights to create tables) and will not be accessable by others.


If this is not possible, use a Pl/Sql table instead of a 'real' one...

 

[request]

Turkbear:

Can you please give me an example of how Pl/SQL table can be used so that I can insert / delete records from it.
I need 2 columns in PL/SQL table - > name , hours.

Please help. thanks.

 

[dsanchez2]

You could add to the table a column that identifies the user or the session.

If using Oracle 9.2, try using a GLOBAL TEMPORARY Table. Data placed on these tables is only available to the session that inserted it.

 

[martincab]

Global temporary table is available from 8.1.7 version
Example:

Create Global Temporary Table table_name ( name varchar2(50), hours number(12)) On Commit preserve rows;

The data in a temporary table is visible ONLY to
the session that inserts the data into the table.

if You don't specify "On Commit preserve rows" the table data will be deleted after commit command;

You cannot specify any referential integrity (foreign key) constraints on temporary tables.


Martin Cabrera
Oracle DBA/Programmer