Updating an oracle sequence nextval
Here’s the code (not sure whether formatting will come through though: — Author: Ben Martinka 8/9/13 CREATE or REPLACE PROCEDURE reset_seq( — p_nxt out number, p_tbl in varchar2, p_new in number DEFAULT -1 — p_min in number DEFAULT 0 ) IS l_new number; l_nxt number; l_cnt number; l_inc number; l_min number; l_key varchar2(256); l_seq varchar2(256); BEGIN — Derive primary key & sequence names from table name — according to database design standards.
l_key := p_tbl || ‘ID’; l_seq := p_tbl || ‘_PK_SEQ1’; IF p_new Sorry, I forgot to implement my final parameter in the above, which also allows you to change the minimum value for the sequence at the same time. If that is zero, or if a negative value and no rows are found in the table (which will assume a sequence starting at zero), then the min value will be forced to -1 regardless of value of p_min or it will fail.
option causes the sequence to be associated with a specific table column, such that if that column (or its whole table) is dropped, the sequence will be automatically dropped as well.
If specified, this association replaces any previously specified association for the sequence.
He is currently managing development for the online media company, Family First.