Using Sequences (Autonumber) in Oracle

This article is written about how to create and drop sequences in Oracle with syntax and examples.

Description

In Oracle, you can create an autonumber subject by the use of sequences. A sequence is an object in Oracle that is used to generate a range sequence. This can be useful when you want to create a special quantity to act as a main key.

Create Sequence

You can also want to create a sequence in Oracle to cope with an autonumber field.

Syntax

The syntax to create a sequence in Oracle is:

CREATE SEQUENCE sequence_name
  MINVALUE value
  MAXVALUE value
  START WITH value
  INCREMENT BY value
  CACHE value;

sequence_name The name of the sequence that you want to create.

Example

Let’s look at an instance of how to create a sequence in Oracle.

For example:

CREATE SEQUENCE supplier_seq
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

This would create a sequence object known as supplier_seq. The first sequence number that it would use is 1 and every subsequent wide variety would increment via 1 (ie: 2,3,4,…}. It will cache up to 20 values for performance.

If you miss the MAXVALUE option, your sequence will robotically default to:

MAXVALUE 999999999999999999999999999

So you can simplify your CREATE SEQUENCE command as follows:

CREATE SEQUENCE supplier_seq
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

Now that you have created a sequence object to simulate an autonumber field, we are going to cover how to retrieve a cost from this sequence object. To retrieve the subsequent fee in the sequence order, you need to use nextval.

For example:

supplier_seq.NEXTVAL;

This would retrieve the next cost from supplier_seq. The nextval announcement wants to be used in a SQL statement. For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.NEXTVAL, 'Kraft Foods');

This insert statement would insert a new report into the suppliers table. The supplier_id area would be assigned the next range from the supplier_seq sequence. The supplier_name field would be set to Kraft Foods.

Drop Sequence

Once you have created your sequence in Oracle, you might find that you want to get rid of it from the database.

Syntax

The syntax to a drop a sequence in Oracle is:

DROP SEQUENCE sequence_name;

sequence_name The identify of the sequence that you want to drop.

Example

Let’s look at an example of how to drop a sequence in Oracle.

For example:

DROP SEQUENCE supplier_seq;

This example would drop the sequence referred to as supplier_seq.

Frequently Asked Questions

One common question about sequences is:

Question: While developing a sequence, what does cache and nocache picks mean? For example, you could create a sequence with a cache of 20 as follows:

CREATE SEQUENCE supplier_seq
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

Or you ought to create the identical sequence with the nocache option:

CREATE SEQUENCE supplier_seq
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  NOCACHE;

Answer: With admire to a sequence, the cache choice specifies how many sequence values will be stored in memory for quicker access.

The downside of developing a sequence with a cache is that if a device failure occurs, all cached sequence values that have not be used, will be “lost”. This outcomes in a “gap” in the assigned sequence values. When the machine comes back up, Oracle will cache new numbers from the place it left off in the sequence, ignoring the so known as “lost” sequence values.

TIP: To recover the misplaced sequence values, you can continually execute an ALTER SEQUENCE command to reset the counter to the right value.

Nocache potential that none of the sequence values are saved in memory. This choice may sacrifice some performance, however, you not come upon a hole in the assigned sequence values.

Question: How do we set the LASTVALUE cost in an Oracle Sequence?

Answer: You can change the LASTVALUE for an Oracle sequence, by using executing an ALTER SEQUENCE command.

For example, if the ultimate value used by means of the Oracle sequence used to be one hundred and you would like to reset the sequence to serve 225 as the subsequent value. You would execute the following commands.

ALTER SEQUENCE seq_name
INCREMENT BY 124;

SELECT seq_name.nextval FROM dual;

ALTER SEQUENCE seq_name
INCREMENT BY 1;

Now, the subsequent cost to be served through the sequence will be 225.