Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


Oracle PL/SQL Sequences

PL/SQL Sequences

This article describes how to create, use, and drop sequences in Oracle RDBMS.

Creating Sequences in Oracle Enterprise Manager Console

Sequences can be created using SQL *Plus or using the Enterprise Manager Console. To create a sequence in the Console, open the database tree and select the Schema you want to add the sequence to, right click on Sequences and click Create.

Enterprise Manager Console Sequence Creation

The Create Sequence window opens. Type a Name for the sequence in the Name TextBox. A Minimum and Maximum value may be entered. The Interval value is the difference between the values produced by the Sequence. An interval of 1 will produce: 1, 2, 3, etc. An interval of 5 will produce: 5, 10, 15, etc. The Initial value is also entered. It defaults to 1, but any value may be entered. Options to Cycle the value allows the values to start over again once the maximum value has been reached. Order values ensures the database provides the values in numeric order. If unchecked, the database does not necessarily provide the values in order. Cache allows the database to store the sequence values and improves performance.

Create Sequence Window

After clicking Create, the database will create the sequence in the Schema.

Sequence Created

Creating SEQUences in Oracle SQL *Plus PL/SQL

To create the sequence in SQL *Plus, login and type the syntax shown. This code creates a sequence named MySeq that starts with the number 2 and increments by 1 and has no maximum value.

Create Sequence Statement

By using the Dual table, a select statement can be written to extract the Next value from the sequence.

NextVal of Sequence

To use the sequence, I have created a table that uses an Integer value for the ID and varchar2 for an additional string field.

Create Table

To insert data and use the sequence, the syntax in the 2nd part of the INSERT statement uses the <sequence name>.NextVal to get the sequence value. I have included a SELECT query on DUAL to get the CurrVal and NextVal of the Sequence after the insert.

INSERT statement of NextVal

To delete the sequence, a DDL Drop statement will remove it.

Drop Sequence

This example uses a Sequence with a minimum value and a maximum value with cycle to show how Oracle reuses the numbers of a sequence once they have all been utilized.

Sequence with Cycle

To populate the table with enough data to run through the sequence values at least 1 time, some anonymous code is used to construct a WHILE loop that will insert the NextVal of the sequence 9 times producing 9 records in the table. A SELECT statement from the table confirms that the data was inserted and that the sequence begins to reuse numbers as of the last record inserted (ID = 1 as the last record).

While Loop to populate Table

Problem - Oracle Is No Longer Able to Create PL/SQL Sequences

Notes: I noticed that the database was unable to create sequences and just seemed to "hang" on the CREATE SEQUENCE statement until I forced a close out of the Console or SQL *Plus. It was not until I turned off the ArchiveLog that the create statements were functional.

To determine if the database is in ArchiveLog mode or NoArchiveLog mode, check the Enterprise Manager Console or in SQL *Plus, check the v$database with a select statement.

Console Manager ArchiveLog PL SQL ArchiveLog Mode

If the database is in ArchiveLog mode, issue a Shutdown Immediate command and start the database in Mount mode and then use an alter database command to put it in noarchivelog mode and another alter database command to start the database. Alternately, unchecking the ArchiveLog box in the Console will cause the database to shutdown and restart.