How To Set Sequence Value In Oracle

---

Oracle / PLSQL: Sequences (Autonumber)

This Oracle tutorial explains how to create and drop sequences in Oracle with syntax and examples.

Description

In Oracle, yous tin create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This tin exist useful when you need to create a unique number to human action as a primary fundamental.

Create Sequence

You may wish to create a sequence in Oracle to handle an autonumber field.

Syntax

The syntax to create a sequence in Oracle is:

CREATE SEQUENCE sequence_name   MINVALUE value   MAXVALUE value   Get-go WITH value   Increment Past value   CACHE value;

sequence_name

The name of the sequence that y'all wish to create.

Case

Permit's look at an case of how to create a sequence in Oracle.

For example:

CREATE SEQUENCE supplier_seq   MINVALUE 1   MAXVALUE 999999999999999999999999999   Kickoff WITH ane   INCREMENT Past 1   Enshroud xx;

This would create a sequence object called supplier_seq. The first sequence number that it would use is 1 and each subsequent number would increment by one (ie: ii,3,iv,...}. It will cache upward to xx values for performance.

If you omit the MAXVALUE option, your sequence volition automatically default to:

MAXVALUE 999999999999999999999999999

Then you can simplify your CREATE SEQUENCE command as follows:

CREATE SEQUENCE supplier_seq   MINVALUE 1   Offset WITH ane   INCREMENT Past i   CACHE twenty;

Now that you've created a sequence object to simulate an autonumber field, we'll embrace how to retrieve a value from this sequence object. To retrieve the adjacent value in the sequence order, you demand to utilize nextval.

For example:

supplier_seq.NEXTVAL;

This would retrieve the side by side value from supplier_seq. The nextval statement needs to exist used in a SQL statement. For instance:

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

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

Drop Sequence

Once you have created your sequence in Oracle, you might observe that you need to remove information technology from the database.

Syntax

The syntax to a driblet a sequence in Oracle is:

Drib SEQUENCE            sequence_name;

sequence_name

The name of the sequence that you wish to driblet.

Example

Let's look at an instance of how to drop a sequence in Oracle.

For example:

Drib SEQUENCE supplier_seq;

This example would drop the sequence called supplier_seq.

Frequently Asked Questions

One common question about sequences is:

Question: While creating a sequence, what does cache and nocache options mean? For example, y'all could create a sequence with a cache of xx as follows:

CREATE SEQUENCE supplier_seq   MINVALUE 1   START WITH ane   INCREMENT By 1   CACHE twenty;

Or you could create the same sequence with the nocache choice:

CREATE SEQUENCE supplier_seq   MINVALUE ane   Offset WITH 1   INCREMENT Past 1   NOCACHE;

Answer: With respect to a sequence, the cache option specifies how many sequence values volition exist stored in retention for faster admission.

The downside of creating a sequence with a cache is that if a system failure occurs, all buried sequence values that accept not be used, volition be "lost". This results in a "gap" in the assigned sequence values. When the system comes dorsum up, Oracle will cache new numbers from where information technology left off in the sequence, ignoring the so called "lost" sequence values.

TIP: To recover the lost sequence values, y'all can always execute an Alter SEQUENCE command to reset the counter to the correct value.

Nocache ways that none of the sequence values are stored in retentiveness. This selection may cede some operation, still, you should not see a gap in the assigned sequence values.

---

Question: How do nosotros fix the LASTVALUE value in an Oracle Sequence?

Answer: You can alter the LASTVALUE for an Oracle sequence, past executing an ALTER SEQUENCE command.

For example, if the last value used by the Oracle sequence was 100 and you would like to reset the sequence to serve 225 as the next value. You would execute the post-obit commands.

ALTER SEQUENCE seq_name Increment BY 124;  SELECT seq_name.nextval FROM dual;  Change SEQUENCE seq_name INCREMENT BY 1;

Now, the side by side value to be served past the sequence will be 225.

Source: https://www.techonthenet.com/oracle/sequences.php

Share this post