-1

I have a table with a column sno having values (1,2,3,4,5). i have created a sequence seq which starts with 1 and increment by 1. now i want to insert the value 6 and so on in this column using this sequence seq but i dont want to change the start with value in the created sequence?

Different ways to do it if possible would be great. Thankyou.

subash thala
  • 1
  • 1
  • "but i dont want to change the start with value in the created sequence?" I don't get you here? Do you mean you don't wanna change the sequence? That won't change right? Or do you mean that when you delete an element the sequence should reset? – frostzt Oct 07 '22 at 05:27
  • I mean by using the created sequence i want to continue any table with existing values – subash thala Oct 07 '22 at 05:31
  • I still don't understand what you mean by that can you please provide an example! – frostzt Oct 07 '22 at 05:32
  • eg - table a col1 (1,2,3,4,5). This table is already existing. Now I want to create a sequence. it should start with 1 ,increment by 1 and all other details doesnt matter. So now using this sequence i want to insert a new row in table a but if i insert it would insert 1 but i dont want 1, i want the next value after 5 that is 6 to be inserted? – subash thala Oct 07 '22 at 05:37
  • If you're talking about the same table then you can use auto increment right? https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle – frostzt Oct 07 '22 at 05:50

2 Answers2

0

The only way I know is what you don't want.

-- find the last `sno` value; let's presume it returns 5
select max(sno) from that_table;

-- create a sequence which starts with max + 1 (i.e. 6):
create sequence seq start with 6;

-- create a trigger which populates `sno`:
create or replace trigger trg_bi_tab
  before insert on that_table
  for each row
begin
  :new.sno := seq.nextval;
end;
/

Note that sequences aren't gapless, which means that it might (and will, sooner or later) produce gaps.

If you badly insist on continuing the sno sequence, then it is literally MAX + 1. Can you do that? Sure, but that's a bad idea because you might (and will) get duplicates whenever two (or more) users fetch the same MAX value; only the first one who commits will store that value - all other users will create duplicates (and, possibly, violate uniqueness if sno column is supposed to be unique).

Yes, you could lock the table until the first user - who is working on that table - does the job and commits, but that's even worse because in a multi-user environment you'll have many not-that-happy users (who are waiting ... and waiting ...).

Or, you could create your own "sequence" - a table that holds the next value. You'd calculate it with an autonomous transaction function.

But, why would you want to do anything of that? Create a sequence that starts with max + 1 and let Oracle do the rest.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

You could alter the sequence. Suppose the highest nr of your table is 35 so the sequence needs to start at 36 and you don't know what the current curval of your sequence is but you want to set it to have 36 as nextval then this is what you could do:

DECLARE
  l_new_curval NUMBER := 35;
  l_curval NUMBER;
  l_offset NUMBER;
BEGIN
  l_curval := myseq.nextval;
  l_offset := l_new_curval - l_curval;
  EXECUTE IMMEDIATE 'alter sequence myseq increment by '||l_offset;
  l_curval := myseq.nextval;
  EXECUTE IMMEDIATE 'alter sequence myseq increment by 1';
END;
/
Koen Lostrie
  • 14,938
  • 2
  • 13
  • 19