ORA-02437: "primary key violated" - why can't I see duplicate ID in SQL Developer?
<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>