Hello folks,
I am sure, a lot of us have seen this error. So, basically what it means is:
ORA-00001: unique constraint (string.string) violated
Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see this
message if a duplicate entry exists at a different level.
Action: Either remove the unique restriction or do not insert the key.
So, I have a situation here. I have an Oracle Form that inserts or updates records into one table. Nothing that complicated. So, there is a Unique Key on the table and its based on the ENTITLEMENT_YEAR and the ENTITLEMENT_ID. The ID is incremented and the Year is the current Year.
Please note that the current_seq_number is not a Oracle Sequence Number but rather a field in a table sq_gen_tb. So, these two fields are generated at PRE-INSERT trigger of the block. So, my question is ... How is it possible to encounter this problem ?
Pre-Insert Trigger
:entiltlement_bk.entitlement_year := 2010; (There is logic in place to get the current year)
select current_seq_number + 1 into t_entitlement_id
from sq_gen_tb
where fiscal_yy = :entiltlement_bk.entitlement_year
and sequence_name = 'ENTITLEMENT_NUM'
for update of current_seq_number;
:entiltlement_bk.entitlement_id := t_entitlement_id;
More Code Here .....
(Please note that there is a situation that because of a certain condition,
it could do a raise_form_trigger_failure)
Table sq_get_tb description is as follows:
PROD_SUPPORT>>>desc sq_gen_tb
Name Null? Type
------------------------------- -------- ----
SEQUENCE_NAME NOT NULL VARCHAR2(20)
FISCAL_YY NUMBER(4)
CURRENT_SEQ_NUMBER NOT NULL NUMBER(9)
I do know that there were 2 users working on the same screen entering records at the same time when the error occurred.
Version Used: Oracle Forms 6i/Oracle DB 9i
Thanks in advance.
Edited by: RDonASnowyDay on Jan 28, 2010 1:18 PM
Edited by: RDonASnowyDay on Jan 28, 2010 1:19 PM