For some reason, people in the past have inserted data without using sequence.NEXTVAL. So when I go to use sequence.NEXTVAL in order to populate a table, I get a PK violation, since that number is already in use in the table.
How can I update the next value so that it is usable? Right now, I'm just inserting over and over until it's successful (
INSERT INTO tbl (pk) VALUES (sequence.NEXTVAL)
These two procedures let me reset the sequence and reset the sequence based on data in a table (apologies for the coding conventions used by this client):
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;