Re: Sequential numbering in Oracle

If you are not sure ORACLE has a feature, try to RTFM. ACCESS does have some fine features. Now, assuming this isn't a troll to start another DB flame war, I'll provide some suggestions where to look.

ORACLE provides a SEQUENCE to deliver a series of numeric values.

When inserting or updating to a table, you can have a TRIGGER that adds the next value from the SEQUENCE to the row. This is a very common practice in ORACLE. The difference is that there may be gaps in the sequence in ORACLE. This is due to the inherent multiuser design of ORACLE. You really shouldn't care if there are gaps in the sequence. (If you do HAVE TO HAVE a continuous sequence, there are ways to control this.)

When loading a table from a flat file, the SQL*Loader also has a SEQUENCE feature. This will give you exactly the same result as ACCESS would, in particular: a sequence with no gaps. Note the SEQUENCE in the loader is different from the SEQUENCE object in the database itself.

There are examples of all of this in the manuals. Have a look and try it.

If you have specific coding problem, post a small example of the code that shows the problem and you will receive lots of help from the fine folks here.

And if you were a troll:
 Just remember: where ACCESS holds your hand and gently steps you through it, ORACLE expects you to do your own work, especially reading the manuals. In the end with ORACLE you can lift much heavier workloads than with ACCESS.

Have a great day.
  Ed Prochak
  Magic Interface, Ltd.
  http://www.magicinterface.com

"trub3101" <michael_at_bassline01.fsnet.co.uk> wrote in message news:<a64rj4$re3$2_at_newsg2.svr.pol.co.uk>...
> Hi everyone,
>
> I have just migrated a MS Access database to Oracle 8i by exporting it to a
> flat comma-seperated file and using SQL*Loader populate the Oracle database.
>
> After much tinkering during populating the Oracle database I now seem to
> have lost the functionality of the primary key (ref) which in Access
> automatically generated the next number for the next record appended to the
> database.
>
> Could anyone just clarify for me whether or not Oracle provides this
> functionality?
> I suspect that it does not, not sure why?
>
> Will I have to write some sort of procedure or routine that does this?
>
> My first inkling is to create a Java singleton class that generates
> consecutive numbers when access and link it to the primary key of the table.
> Am I mad?
>
> Thanks (in advance)
>
> tb3101
Received on Wed Mar 06 2002 - 11:18:30 CST