Fix non-unique PK id Oracle



DECLARE
  CURSOR sqlCur IS SELECT id FROM (SELECT id, count(1) cnt FROM <someTable> GROUP BY id) WHERE cnt > 1;
  recId <someTable>.id%TYPE;
  newId <someTable>.id%TYPE;
  fixSql VARCHAR2(500);

  CURSOR dupeCur (recId <someTable>.id%TYPE) IS SELECT id FROM <someTable> WHERE id=recId FOR UPDATE of id;
BEGIN
    DBMS_OUTPUT.PUT_LINE('<someTable> checking and fixing dupe ids before creating PK');

    --update 1 record from each id with a dupe
    --repeat until none remaining
    <>
    LOOP
      OPEN sqlCur;
      FETCH sqlCur INTO recId;
      EXIT dupe_detect WHEN sqlCur%NOTFOUND;
 
      <>
      LOOP
        SELECT <someTable>_seq.nextval INTO newId FROM dual;
        FOR rec IN dupeCur(recId) LOOP
          --update only the first record and exit
          UPDATE <someTable> SET id = newId WHERE CURRENT OF dupeCur;
          EXIT;
        END LOOP;
        COMMIT;
   
        FETCH sqlCur INTO recId;
        EXIT dupe_fix WHEN sqlCur%NOTFOUND;
      END LOOP dupe_fix;
 
      CLOSE sqlCur;
 
    END LOOP dupe_detect;
 
    --create pk
    DBMS_OUTPUT.PUT_LINE('<someTable> creating PK - may take awhile');
    EXECUTE IMMEDIATE 'ALTER TABLE <someTable> ADD CONSTRAINT <someTable>_PK PRIMARY KEY (id) USING INDEX TABLESPACE &&2';
END;
/

Comments

Popular posts from this blog

Sites, Newsletters, and Blogs

Oracle JDBC ReadTimeout QueryTimeout