title: ORA-02437: "primary key violated" - why can't I see duplicate ID in SQL Developer?
body: <p>I would receive an error:</p>
  
  <pre><code>ORA-02437:  cannot validate  (%s.%s) - primary key violated
  Cause:  attempted to validate a primary key with duplicate values or null values
  </code></pre>
  
  <p>I found it was because I have a stored procedure that increments the ID, but it had failed to do so when it re-ran and had an error related to one of my datatypes.  I found I now had a duplicate ID in my database table.  All this made sense and I was able to easily rectify it with a <code>DELETE FROM MyTable WHERE ID = x</code>, where <code>x</code> was the offending duplicate ID.  <em>The problem I have</em> is the only way I was able to even find the IDs that were duplicated is in the first place is because I did a <code>SELECT * FROM MyTable WHERE ID = x</code> -- where <code>x</code> was one greater than the last ID I could actually see.  I found it just by an educated guess.  So:</p>
  
  <ol>
  <li>Why can't I see these duplicate IDs when I open the table in Oracle SQL Developer?  It only shows the last row as the ID before the duplicates.  I don't think it is because of my primary key constraint, since the first line in my stored procedure is to remove that (and put it back, at the end - probably when I got my error), and it was not present when I looked at my table.</li>
  <li>Is there some way to make these last IDs that got inserted into the table visible, so I wouldn't have to guess or assume that the duplicate IDs are "hiding" as one greater than the last ID I have in my table, in the future?  There is a <code>commit;</code> in my stored procedure, so they should have appeared -- unless, of course, the procedure got hung up before it could run that line of code (highly probable).</li>
  </ol>
  
  <p>Stored procedure that runs:</p>
  
  <pre><code>create or replace 
  PROCEDURE PRC_MYTABLE_INTAKE(
  , EMPLOYEE_ID IN NVARCHAR2
  , TITLE_POSITION IN NVARCHAR2
  , CREATED_DATE IN DATE
  , LAST_MODIFIED IN DATE
  ) AS
   myid integer := 0;
   appid integer := 0;
  BEGIN
  -- disable PK constraint so it can be updated
  EXECUTE IMMEDIATE 'ALTER TABLE MYTABLE DROP CONSTRAINT MYTABLE_PK';  
  COMMIT;
  -- assign ID to myid
  SELECT ID INTO myid FROM MYTABLE WHERE ROWID IN (SELECT MAX(ROWID) FROM MYTABLE);
  -- increment
  myid := myid + 1;
  -- assign APPLICATION_ID to appid
  SELECT APPLICATION_ID INTO appid FROM MYTABLE WHERE ROWID IN (SELECT MAX(ROWID) FROM MYTABLE);
  -- increment
  appid := appid + 1;
  -- use these ids to insert with
  INSERT INTO MYTABLE (ID, APPLICATION_ID, 
  , EMPLOYEE_ID
  , TITLE_POSITION
  , CREATED_DATE
  , LAST_MODIFIED
   ) VALUES(myid, appid, 
  , EMPLOYEE_ID
  , TITLE_POSITION
  , CREATED_DATE
  , LAST_MODIFIED
  );
  COMMIT;
  -- re-enable the PK constraint
  EXECUTE IMMEDIATE 'ALTER TABLE PASS ADD CONSTRAINT MYTABLE_PK PRIMARY KEY (ID)';
  COMMIT;
  END;
  </code></pre>