CODEWITHSUNDEEP
sqloracle-database
Shades
Shades

Reputation: 285

Sequence incrementing every 4 id's instead of every 1 like I set it to. Oracle

I am new to SQL and Oracle so for practice, I have created a dummy table I made to track my typing learning sessions(because I never learned to type so i'm making up for it now), and set a sequence up using this query in Oracle:

CREATE SEQUENCE seq_keyboard_learning
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10

My intent was for my id column to increment by but it jumped from 1 to 5, etc every time i add a new value. For completeness, here are some of the queries i used when setting up this table.

CREATE TABLE keyboard_learning
(
emplid NUMBER CONSTRAINT emplid_pk PRIMARY KEY
,WPM NUMBER
,date_completed DATE
)

CREATE SEQUENCE seq_keyboard_learning
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10

INSERT INTO keyboard_learning (emplid,wpm,date_completed)
VALUES (seq_keyboard_learning.nextval,15,'12-JUN-2012')

UPDATE keyboard_learning 
SET emplid = 1
WHERE emplid = 4

ALTER TABLE keyboard_learning
ADD attempt VARCHAR2(45)

INSERT INTO keyboard_learning
VALUES (seq_keyboard_learning.nextval,26,'6-JUN-2012','ASDFJKL:',2)

instead of incrementing every 4 terms, how can i adjust? Thanks

Upvotes: 2

Views: 2089

Answers (1)

Nick
Nick

Reputation: 2514

Ensuring you have a no gap sequence is basically impossible. Keep in mind, a get from a sequence is an atomic operation, so if you go to insert a record and encounter an error, the sequence will still get incremented. See below example.

Having a cache can also cause you to "lose" sequences. If I specify a value of 10 in my cache, the database will cache 10 from the sequence. If you only insert 2 rows and shutdown the database, the other 8 are discarded. Note: Edited with correction by Alex Poole.

I hope this helped understand some of the behavior of sequences.

create table test
(id     number,
my_date date);

select seq.currval from dual;

insert into test
(id, my_date)
values (seq.nextval, 'foo'); -- will throw an exception

select seq.currval from dual;

Which results in:

table TEST created. 
CURRVAL
-------
      1 


Error starting at line 31 in command: insert into test (id, my_date) values (seq.nextval, 'foo') Error report: SQL Error: ORA-01858: a non-numeric character was found where a numeric was expected
01858. 00000 -  "a non-numeric character was found where a numeric was expected"
*Cause:    The input data to be converted using a date format model was
           incorrect.  The input data did not contain a number where a number was
           required by the format model.
*Action:   Fix the input data or the date format model to make sure the
           elements match in number and type.  Then retry the operation. 
CURRVAL
-------
      2

Upvotes: 3

Related Questions