Re: Reseting Sequence

Ok, here is a routine which might be helpful. You can easily alter it reset to zero (instead of the sequence from the table). Just:

1) get the curval from the sequence
2) alter the sequence to have increment as the curval * -1
3) get the nextval from the sequence
4) alter the sequnce to have increment as 1

--
-- Alter a sequence to have next value 1 greater than highest value in a
table
--
declare
Handle Integer;
SqlText varchar2(2000);
NewHighval Integer;
Workint Integer;
Begin
 select max(job_id) into NewHighVal from job_posting;
 select job_posting_seq.nextval into workint from dual;
 If NewHighVal > WorkInt then
  WorkInt := NewHighVal - WorkInt;
 Else
  WorkInt := (WorkInt - NewHighVal) * -1;
 End if;
 SqlText := 'Alter Sequence job_posting_seq increment by ' ||
to_char(Workint);
 Handle := dbms_sql.open_cursor;
 dbms_sql.parse(handle, SqlText, DBMS_SQL.NATIVE);
 select job_posting_seq.nextval into workint from dual;
 SqlText := 'Alter Sequence job_posting_seq increment by 1' ;
 dbms_sql.parse(handle, SqlText, DBMS_SQL.NATIVE);
End;



Nermin Tanovic wrote:



> What would be the best way to reset the curent value of the sequence

> and set it back to 0 from PL/SQL script?

>

> Since Oracle does not have a direct way to do this (which I find

> strange),

> the solution has to be some way around.

>

> I would apretiate your input

>

> thanks and happy coding

> Nermin