Re: create unique tablename?

In article <37em3v$hu6_at_lastactionhero.rs.itd.umich.edu>, Kent J. Siemers <kjs_at_umich.edu> wrote:
>PC (pc_at_wellington.mafqual.govt.nz) wrote:
>
>: 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.
>
>A pretty standard thing to try is to include the System Date, including the
>hours, minutes, and milliseconds, into the table name. This scenerio works
>for situations where multiple users are not concurrently trying to do this.
>(In other words, you won't get two users with the same number for
>milliseconds in the System Date.)
>
>Kent Siemers
>Database Administration, University Information Systems
>The University of Michigan

Another way is to create a sequence in the database, and include the 'nextval' as part of the table name, as in:

select 'create table tab'||seqname.nextval||' column xxx varchar2(10));' from dual;

This will return:

create table tab1 column xxx varchar2(10));

...and each query of the sequence will return a unique number value.

HTH

--
============================================================================
Mike Vergara           |   Be good...and you will be lonesome
vergara_at_nosc.mil       |                                       Jimmy Buffett
Opinions expressed are not necessarily those of anyone else but me.  So there.