Creating and Using Sequence in Oracle Database

A sequence is used to auto-generate numbers in ascending or descending order. It can be used to auto-generate a row identifier (primary key) or can be a part of a composite primary key (primary key based on more than one column).


Creating a Sequence

Syntax:
CREATE SEQUENCE <SEQUENCE_NAME>
[INCREMENT BY <VALUE>]
[START WITH <VALUE>]
[MAXVALUE N|NOMAXVALUE]
[MINVALUE N|NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE N|NOCACHE];
Example:
CREATE SEQUENCE MYSEQ;

The example shown above is the simplest way to create a sequence. Its initial value will be set to 1 and will be incremented by 1.


Nextval & Currval

CURRVAL and NEXTVAL are two attributes of a sequence used to get the current value of the sequence and to increment the sequence respectively.

Example:
MYSEQ.CURRVAL --  Returns the current value
MYSEQ.NEXTVAL --  Increments the sequence and returns the next value

Note: Once a sequence is created CURRVAL is used to initiate the sequence and set its value to the default value.

Using Increment By, Start With, Maxvalue & Minvalue

Increment By is used to set the increment step.

Start With sets the default value.

Minvalue is used in case of descending sequence to set the minimum value up-to which a sequence can go. In case of ascending sequence min-value is same as start value, and hence either of the two can be used to set the default value.

Maxvalue is similarly used in case of ascending sequence to set the maximum value. Same as Start With in case of descending sequence.

Examples:

CREATE SEQUENCE MYSEQ
INCREMENT BY 2
START WITH 10
MAXVALUE 30;

The sequence would generate values as: 10, 12, 14, 16 ... 30

CREATE SEQUENCE MYSEQ
INCREMENT BY -5
START WITH 100
MINVALUE 30;

The sequence would generate values as: 100, 95, 90, 85 ... 30

Using Cycle & Cache

Cycle is used to create a cyclic sequence i.e. it would repeat after it has reached the maxvalue.

Cache is used to set the number of times the sequences should be cycled/ repeated.

Example:
CREATE SEQUENCE MYSEQ
MAXVALUE 10
CYCLE
CACHE 3;

The above example will create a sequence which will cycle and repeat from 1 to 10 for 3 times.


Using Sqeuence in a Table

A sequence can be used in an INSERT statement while inserting data into the table as shown below:

Example:
INSERT INTO EMP(EMPID, NAME) VALUES(MYSEQ.NEXTVAL, 'CARL');

Each time a record is inserted using the above statement, the EMPID column will be populated with an incremented value.


Modifying a Sequence

We can modify an existing sequence using the ALTER statement. Any property of the sequence can be modified, except START WITH.

Example:
ALTER SEQUENCE MYSEQ
MAXVALUE 40;

ALTER SEQUENCE MYSEQ
NOMAXVALUE;

Deleting a Sequence

A sequence can be deleted using DROP statement.

Example:
DROP SEQUENCE MYSEQ;
POPULAR ARTICLES

Creating Conditional Beans in Spring

The concept of condition beans enables Spring to restrict the creation of any bean depending on the evaluation of a condition. These beans get created only when a preset condition is evaluated as true

View Article

Accepting Request Param and Path Variable in Spring Controller

Spring MVC provides various ways through which a client browser can pass data to the Controller. In this article we will discuss about accepting Request Parameters and Path Variables in Spring Contr..

View Article

Generate Namespace & Schema Information using JAXB

Most xml documents used in enterprise applications makes use of namespace to avoid element name conflicts. This article talks about generating these namespace and schema information when marshaling...

View Article

Switching Database Profile using Spring Profiles

We are most likely to have separate db configuration for different environment like development and production environment. Spring profiles provide a convenient way to switch db profiles at runtime.

View Article

SQL and its Sub-Languages

SQL (Structured Query Language) is a language understood by most modern databases. It is an ANSI (American National Standard Institute) standard language which is used to manipulate databases.

View Article

Introducing JUnit Rule

Junit Rules allows developers to add additional functionalities that can applied to all test methods in a test class. It is similar to the concept of custom test runners but with reduced restrictions.

View Article

Addressing Ambiguity in Spring Autowiring

Spring autowiring is powerful concept, but we should be very cautious while using it. We may end up in creating ambiguity while autowiring beans, which will cause autowiring to fail.

View Article

Creating and Using Synonym in Oracle Database

Synonyms are database objects used to provide duplicate names to existing objects in the database. It is just an alternate name used to hide the original name of the object.

View Article

Creating and Using Sequence in Oracle Database

A sequence is used to auto-generate numbers in ascending or descending order which can serve as a primary key or a part of it (in case of composite key).

View Article

Creating and Manipulating Constraints in Oracle Database

Constraints are used to impose certain rules on columns to avoid invalid data entry into the table. If any of the constraint is violated the operation fails.

View Article

Integrating Log4J with Perf4J for Performance Logging

Perf4j is an open source logging framework used primarily for monitoring performance statistics in java applications. Log4j has the ability to integrate with perf4j to capture performance data.

View Article

Tagging in GIT

Tagging allows us to mark a specific point in the commit history or snapshot. A tag is typically used to mark a project release. This article shows how to create tags in Git.

View Article