Oracle Sequences are a unique animal. This seems to get a lot of developers. We had one issue at work today. I will post that separately.
If you come from other databases, you are probably used to Auto Incrementing columns. Oracle does not have that. Short of that, it has a Sequence.
A Sequence is just a counter – a separate object that exists in the schema. It has no connection to any specific column, but by naming convention, we tend to associate it to a particular index column in a table. All it is, is a counter that gets bumped up each time you call a
function (NextVal) to increment it. In a typical production Oracle database, you will find a lot of Sequence objects, one for each ID (primary index) column in the tables.
Usage of sequence is somewhat like shown in the diagram below.
A sequence is an Oracle Schema object that has the following:
A Start or Initial Value is the value the Sequence will have initially. Current Value is the counter’s value at any time. A Sequence also has a max. Value that it can increment to, after which it can be made to recycle back to 0 (or 1).
So, you can see how it works – all you have to do is to get the current value and bump it up each time. That’s exactly what the NextVal function does. As a safe guard, you cannot use CurrentVal until you call NextVal first, so you don’t end up using a previously used value, even by accident.
Here are some examples:
CREATE SEQUENCE customers_seq START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;
See here for a full explanation. When you don’t need it any longer, just DROP it.
As for using the Sequence, you just have to do Sequence.NextVal. To do this in SQL, you can use DUAL table,
SELECT customers_seq.NextVal FROM dual;
Or you can simply embed in an INSERT statement, where it’s typically needed (when you are creating a new record in the table).
INSERT INTO customers(customer_id, customer_name) VALUES(customers_seq.NextVal, 'Test');
This site talks about using it in various scenarios of PL/SQL.
Sometimes, you may want to change a sequence – to do this, you use ALTER Sequence. ALTER is used even for incrementing the Sequence counter. See here.