We've run into ORA-00001 while executing a stored procedure in production last time. The stored procedure was working fine until yesterday, and I tried to troubleshoot but didn't get anywhere.
Appreciate if you can provide some useful thoughts on below..
I've breakdown the situation into: 1) Table with primary key, 2) A sequence 3) A stored procedure.
1) We have main table as below:
CREATE TABLE MY_MESSAGES (MESSAGE_ID NUMBER, MESSAGE VARCHAR2(200));
CREATE UNIQUE INDEX MY_MESSAGES_PK ON MY_MESSAGES (MESSAGE_ID);
ALTER TABLE MY_MESSAGES ADD CONSTRAINT MY_MESSAGES_PK PRIMARY KEY (MESSAGE_ID) USING INDEX ENABLE;
2) A sequence
CREATE SEQUENCE MESSAGE_ID_SEQUENCE;
An independent Backup table:
CREATE TABLE MY_MESSAGES_BKP (BKP_ID VARCHAR2(200), RECIVED_TIME TIMESTAMP, MESSAGE VARCHAR2(200));
INSERT INTO MY_MESSAGES_BKP VALUES('201', TIMESTAMP '2018-09-26 00:00:00.000000', 'MSG206');
INSERT INTO MY_MESSAGES_BKP VALUES('202', TIMESTAMP '2018-09-26 05:00:00.000000', 'MSG206');
INSERT INTO MY_MESSAGES_BKP VALUES('203', TIMESTAMP '2018-09-26 06:00:00.000000', 'MSG207');
INSERT INTO MY_MESSAGES_BKP VALUES('204', TIMESTAMP '2018-09-26 07:00:00.000000', 'MSG208');
INSERT INTO MY_MESSAGES_BKP VALUES('205', TIMESTAMP '2018-09-26 08:00:00.000000', 'MSG209');
COMMIT;
3) And finally, the stored procedure:
DECLARE
TYPE VARCHAR_TABLE IS TABLE OF VARCHAR(200);
V_MESSAGE_ID NUMBER(20) := 0;
V_BKP_IDS VARCHAR_TABLE := VARCHAR_TABLE();
V_EXC_QUERY VARCHAR2(200) := 'INSERT INTO MY_MESSAGES(MESSAGE_ID, MESSAGE) SELECT :1, MESSAGE FROM MY_MESSAGES_BKP WHERE BKP_ID = :2';
BEGIN
SELECT BKP_ID BULK COLLECT INTO V_BKP_IDS FROM MY_MESSAGES_BKP WHERE RECIVED_TIME > TIMESTAMP '2018-09-26 00:00:00.000000';
FOR I IN 1..V_BKP_IDS.COUNT LOOP
EXECUTE IMMEDIATE 'SELECT MESSAGE_ID_SEQUENCE.NEXTVAL FROM DUAL' INTO V_MESSAGE_ID ;
EXECUTE IMMEDIATE V_EXC_QUERY USING V_MESSAGE_ID, V_BKP_IDS(I);
END LOOP;
V_BKP_IDS.DELETE;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
Knowing that, the table MY_MESSAGES is used by other processes that uses the same sequence (MESSAGE_ID_SEQUENCE) for the primary key..
The stored procedure was running for some time and it inserted more than 400 records out of ~5000 records.. then it stopped with below error:
ORA-00001: unique constraint my_messages_pk) violated
Moreover, after investigating the table we've found that all primary keys of the successfully inserted records were sequential before it stopped..
The other process was running perfectly fine in the background, and we can see the inserted records are also sequential but with 1 number gap between last record inserted by the stored procedure and the next inserted record by the other process.
Which means that the MESSAGE_ID_SEQUENCE.NEXTVAL was executed by the stored procedure, but no record was inserted.
The tables doesn't have a record with a primary key were the stored procedure stopped.
What could have possibly gone wrong? And how can we investigate further?
the most straight forward explanation would be that in the case of the ORA-00001, this select SELECT :1, MESSAGE FROM MY_MESSAGES_BKP WHERE BKP_ID = :2 found more than one row with BKP_ID = :2. This would in turn cause the insert INSERT INTO MY_MESSAGES(MESSAGE_ID, MESSAGE) SELECT :1, MESSAGE FROM MY_MESSAGES_BKP WHERE BKP_ID = :2 to use the same number from the seuquence several times. HTH