CREATING AN AUTO-INCREMENTING COUNTER or ORACLE SEQUENCE

Each table has an ID Field which will be used as the Unique Index or Primary Key. Oracle has a useful feature called the Sequence Generator which I've used for this purpose. There are several parameters that can be set when creating a new Sequence of Integers. You can determine the Starting Integer, how to Increment the Sequence, whether the Sequence will have a Cycle (An Ascending Sequence cycles upon reaching its Maximum Values. Conversely, a Descending Sequence cycles upon reaching its Minimum Value.), and finally the Cache Paremeter which allows you to generate a cached number of integers all at one time to reduce I/O when applications use the sequence.
NOTE: Disk I/O is necessary because Oracle needs to keep track of a Sequence's current number in the Data Dictionary. If you Cache Sequence Numbers, some may be lost when, for example, the database server shuts down. However, Primary Keys should have no particular meaning other than to uniquely identify rows in a table.

I have created a simple Sequence for each table. I have not set a Minimum, Maximum or indicated a Cycle, nor will I Cache. The Sequence starts with One and Increments by one for each new row that is inserted into the respective table.

NOTE: For purposes of clarity, I have listed the items on individual lines. However, it is not necessary to use this convention in SQL Plus/Oracle. You may have one long statement; however, this format is much more readable.

Create or Replace Sequence CustomerIDSequence

Start with 1

Increment by 1

NoMinValue

NoMaxValue

NoCycle

NoCache

NOTE: If there are a large number of clients using your application, it is wise to cache sequence numbers for each. This would enhance the speed of your application by eliminating the need to run the Sequence each time a new record is being generated.

NOTE: Prior to implementing the Sequence for use in our VB Application, it is necessary to initialize the Sequence in Oracle. It is also a good idea to test in order to insure that your Sequence is functioning properly. To do so, in the SQL Plus window, type in something similar to the following:

SELECT STOCKIDSEQUENCE.NEXTVAL FROM DUAL;

You should see the next available number in the Sequence displayed as a result.

In order to implement the aforementioned when inserting new records to generate a new sequence number, simply reference the Sequence with the pseudo-column NextVal.

For Example:

Insert Into Customer

Values(CustomerIDSequence.NEXTVAL, .....)