Start Updating an oracle sequence nextval

Updating an oracle sequence nextval

The Oracle NEXTVAL function is used to retrieve the next value in a sequence.

He is certified as Oracle Certified Professional (OCP) and is awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for his continuous contributions to the Oracle users community.

Right now, I'm just inserting over and over until it's successful (CREATE OR REPLACE PROCEDURE SET_SEQ_TO(p_name IN VARCHAR2, p_val IN NUMBER) AS l_num NUMBER; BEGIN EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num; -- Added check for 0 to avoid "ORA-04002: INCREMENT must be a non-zero integer" IF (p_val - l_num - 1) !

= 0 THEN EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by ' || (p_val - l_num - 1) || ' minvalue 0'; END IF; EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num; EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by 1 '; DBMS_OUTPUT.put_line('Sequence ' || p_name || ' is now at ' || p_val); END; CREATE OR REPLACE PROCEDURE SET_SEQ_TO_DATA(seq_name IN VARCHAR2, table_name IN VARCHAR2, col_name IN VARCHAR2) AS nextnum NUMBER; BEGIN EXECUTE IMMEDIATE 'SELECT MAX(' || col_name || ') 1 AS n FROM ' || table_name INTO nextnum; SET_SEQ_TO(seq_name, nextnum); END; will set the current sequence value to the max(pk) of your table (i.e. Bad side : this solution prints max(pk) rows of output, i.e.

When the system comes back up, Oracle will cache new numbers from where it left off in the sequence, ignoring the so called "lost" sequence values.