Re: create unique tablename?

In article <pc.2.1909FF77_at_wellington.mafqual.govt.nz>, pc_at_wellington.mafqual.govt.nz (PC) wrote:

> hi
>
> i need to create a unique tablename, do some inserts and drop it from a sql
> script.
>
> is there an easy way without doing this type of thing:
>
> spool create_table.sql
> select 'create table '||'some unique string' etc etc;
> spool off
> _at_create_table.sql
>
> the script will be running from a cron job at anytime and all users who
 run it
> will be logging on to oracle under the same userid.

instead of doing this, i suggest you use a global table that everyone can get at. dropping and creating tables on a regular basis is not a good idea for several reasons, e.g., it requires the user to have some kind of quota etc., it fragments the system tablespace. also, it's hard to make reliable for the reasons you have noticed.

if what you need is a temporary table, there are two ways to achieve this that i have used:

1. use a canonical table, e.g., work_table, defined globally and accessible to everyone who needs it with select, insert, update, and delete. make sure you delete all inserted rows *before* you ever commit. that way the inserted rows will never be visible to other users.
2. if you need to commit before the delete, you will need a more complex structure:

   create table work_table
     (username varchar(30) default user, other_data.....);

   create view work as
   select other_data...
     from work_table
    where username = user
     with check option;

  grant select, insert, update delete on work to public;

this only works in oracle 7. you can do it in oracle 6 if you include the username in the view and always supply it on inserts. it's just a little more elegant in oracle 7. Received on Tue Oct 11 1994 - 18:48:20 CET