Sequence insight

ORA-02287

Sequence fit into many business requirement where we need to implement incremental logic for a integer column value.

Using two pseudo columns, we increment and fetches sequence values.

NEXTVAL – Increment sequence value
CURRVAL – Reuse current incremented value of sequence.

With Sequence we can define below properties
1. CACHE /NOCACHE
2. ORDER / NOORDER
3. CYCLE / NOCYCLE
and more..

We won’t discuss on Sequence basic, for same please check Here.

Lets start our discussion.

1. SEQUENCE Increment at Row level and Not at Column Level

To make you understand above, let jump to field work.

Let’s Create a ascending and descending sequence.

CREATE Sequence Seq_1 Start With 1 increment BY 1; -- Ascending sequence
CREATE Sequence Seq_2 Start With -1 increment BY -1; -- Descending sequence

Lets run below SQL,

Select Seq_1.Nextval , Seq_1.Nextval , Seq_1.Nextval , Seq_2.Nextval, Seq_2.Nextval
from dual;

In a single SQL statement, we are performing multiple NEXTVAL for sequences.

Point to notice is
1. Whether Sequence will increment for every call to NEXTVAL
OR
2. Whether it will increment once for each row return.

Lets check what’s the output.

NEXTVAL NEXTVAL NEXTVAL NEXTVAL NEXTVAL
---------- ---------- ---------- ---------- ----------
1 1 1 -1 -1

Though NEXTVAL was performed multiple time in the same statement.
It incremented sequences only once.

Sequence increment is entitle to Rows return from statement and not for each NEXTVAL Operations.
So clearly though we had multiple call to NEXTVAL but sequence value was incremented once.

So virtually above SQL is equal to

Select Seq_1.Nextval , Seq_1.Currval , Seq_1.Currval , Seq_2.Nextval, Seq_2.Currval
from dual;

Plan for SQL :
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)| |
| 1 | SEQUENCE | SEQ_1 | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
------------------------------------------------------------------

SEQUENCE is Executed as “SCALAR”, It is fetch for each rows return for the SQL.
Further look at raw trace for SQL executed, we identified below recursive SQL.

1.select increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$,flags,partcount
from seq$ where obj#=:1; /*get metadata for sequence */

2.update seq$
set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,
highwater=:8,audit$=:9,flags=:10,partcount=:11
where obj#=:1;/* update highwater for sequence based on values fetch*/

Both Recursive statement were executed only once for each SEQUENCE.

2. Sequence can’t be part of Scalar SQL.

Let’s run SQL, making it as scalar query accessing Sequence.

Select (Select Seq_1.Nextval From Dual ) Col1
from dual;

After running SQL, we are bound to get below error.

Output:
SQL Error: ORA-02287: sequence number not allowed here
02287. 00000 - "sequence number not allowed here"
*Cause: The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
here in the statement.
*Action: Remove the sequence number.

Incrementing or trying to fetch the current value of the sequence using a scalar query is not allowed.
It will throw exception saying “inappropriate here in the statement.”

Sequence increment or current value access within Scalar sql is not allowed.

Also sequence pseudo columns CURRVAL and NEXTVAL cannot be used at following.

• A sub query
• A view query or materialized view query
• A SELECT statement with the DISTINCT operator
• A SELECT statement with a GROUP BY or ORDER BY clause;
• A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator
• The WHERE clause of a SELECT statement
• DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement** (only for pre Oracle 12c)
• The condition of a CHECK constraint

Reference Oracle doc.

Please note::
With Oracle 12c, we can define default as sequence next value.

Create Table Seq_Default
(
Incre_No Number Default Seq_1.Nextval
);
Table Created.

Insert into Seq_Default values (default);
1 rows inserted.

For more information on same, please Check Here.

3. Sequence Start with Zero.

We are trying to create ascending sequence Starting with Zero and increment by one.
When trying to create sequence starting with ZERO and increment by 1, got an Error.

CREATE Sequence Seq_1 Start With 0 increment BY 1;

Error:
SQL Error: ORA-04006: START WITH cannot be less than MINVALUE
04006. 00000 – “START WITH cannot be less than MINVALUE”
*Cause: the given starting value is less than MINVALUE
*Action: make sure that the starting value is >= MINVALUE

When we increment by positive values, i.e. ascending values, by default MINVALUE is Set as “NOMINVALUE”.
By Default NOMINVALUE is SET as 1 for Ascending sequence.
Also Start Value for any ascending sequence has to be Equal to or Greater than MINVALUE.

Over here START WITH :=0 is NOT <= NOMINVALUE :=1
Hence Error.

To rectify Same and achieve Start with ZERO, we need to specify MINVALUE.

CREATE Sequence Seq_1 Start With 0 increment BY 1 minvalue 0;
sequence SEQ_1 created.

So first NEXTVAL, now will produce output as 0.

SELECT seq_1.nextval FROM dual;

NEXTVAL
——-
0

We can observe similar behavior with Descending Sequence.

CREATE Sequence Seq_2 Start With 0 increment BY -1;

Error:
SQL Error: ORA-04008: START WITH cannot be more than MAXVALUE
04008. 00000 – “START WITH cannot be more than MAXVALUE”
*Cause: the starting value would be larger than MAXVALUE
*Action: make sure that the starting value is less than MAXVALUE

As we have not specified MAXVALUE, it defaults To “NOMAXVALUE”.
NOMAXVALUE for descending sequence is -1. Hence error.

CREATE Sequence Seq_2 Start With 0 increment BY -1 maxvalue 0;

select seq_2.nextval from dual;

NEXTVAL
——-
0

Notes from Oracle doc.

MAXVALUE
Specify the maximum value the sequence can generate.
This integer value can have 28 or fewer digits.
MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE.

NOMAXVALUE
Specify NOMAXVALUE to indicate a maximum value of 1027 for an ascending sequence or -1 for a descending sequence.
This is the default.

MINVALUE
Specify the minimum value of the sequence. This integer value can have 28 or fewer digits.
MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.

NOMINVALUE
Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -1026 for a descending sequence.
This is the default.

4. Increment NEXTVAL of Sequence to some Custom values.

Most of time we have requirement to start NEXTVAL of sequence to some random values different then what would be set as per current increment By.

Suppose Currval of Sequence is 100 and increment by is defined as 1.
So NEXTVAL will give us 101, but we require it to be 201.

Option 1 :
In a loop, do NEXTVAL till Sequence reach 200.

Option 2 : Better Option.

ALTER SEQUENCE seq_name
INCREMENT BY 100; -- alter increment by to 99

Select seq_name.nextval from dual; — current value set to 100 + 100 (increment value)

ALTER SEQUENCE seq_name
INCREMENT BY 1; — reset increment value back to what was before!!

Hope information share were new and any feedback most welcome.!

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s