Oracle Sequence Nextval Function

In Oracle, a sequence refers to a database object that generates a series of numeric values according to a specified increment. Sequences are commonly used to generate the unique primary key values for records in a database table.

In this tutorial, we will learn how to use the NEXTVAL Function when working with Oracle sequences.

NOTE: This tutorial does not cover the basics of creating an Oracle sequence. Refer to our tutorial on Oracle sequences to discover more.

Oracle Nextval Function

The nextval function in an Oracle sequence is used to fetch the next value in a given sequence.

We can express the syntax of the function as shown in the following code snippet:

sequence_name.nextval

The function does not accept any argument or parameter. It then returns the next value in the defined sequence.

Example Function Demonstration

Let us take into consideration an example. We start by defining a simple Oracle sequence as shown in the following code:

CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;

We use the CREATE SEQUENCE statement to initialize a new Oracle sequence. We then define at what value the sequence starts and the increment value for each new generated value.

In our example, the test_sequence starts at the value of 1 and increments by one on each new value. This should generate a series of numerical values starting at 1,2,3,4,5…etc.

Using the Oracle Nextval Function

We can use the next value function from the test_sequence sequence to get the next value, as shown in the following:

select test_sequence.nextval from dual;

This should return the next value from the sequence as shown in the following:

If you call the statement again, it should return the next value in the series which is 2.

This continues until the values are exhausted, or you hit the maximum value which is defined in the sequence. Check our Oracle sequence tutorial to learn how to set the minimum and maximum values.

Using the Nextval Function to Loop Over Values

We can also use the nextval function to print the numbers from 1 to 10 as shown in the following code:

CREATE SEQUENCE looper_sequence
START WITH 1
INCREMENT BY 1;

set SERVEROUTPUT on;
BEGIN
FOR i IN 1..10
LOOP
DBMS_OUTPUT.PUT_LINE(looper_sequence.nextval);
END LOOP;
END;

The provided code creates a new sequence called looper_sequence that starts at 1 and increments by 1 for each new generated value.

We then enable the SERVEROUTPUT option which allows the DBMS_OUTPUT package to display the messages in the SQL*Plus console.

Finally, we enclose a for loop inside an end/begin statement to iterate over the range of values from 1 to 10. We then call the DBMS_OUTPUT.PUT_LINE function for each value in the range and print the next value in the looper_sequence sequence to the console.

The code prints the next ten values in the looper sequence. In our case, it will be the value from 1 to 10 or 11 – 20… and so on for each new call.

Resulting Output:

1
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.

Using the Nextval Function in an Insert Statement

We can also use the nextval function in an insert statement as a primary key. For example:

insert into users(id,first_name,credit_card,country)
values (test_sequence.nextval, 'James Smith', '4278793631436711', 'United Arab Emirates');

In the given example, we call the nextval function from the test_sequence to insert the value for the id column in the table.

Conclusion

In this post, you discovered how to use the Oracle nextval function to fetch the next value in a sequence. You also learned how to use the function to iterate over a set of values or to insert a unique value in a table column.