sqloracle-databaseoracle10gprimary-keysequence

Best way to reset an Oracle sequence to the next value in an existing column?


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)), and that syncs up the nextval.


Solution

  • 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;