SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-20019: ORA-20014: ORA-00001: unique constraint violated

BS2012Oct 4 2013 — edited Oct 4 2013

Hi Everyone, My DB version is

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

Please do have a look at those error codes I'm getting in production.

First of all, I've no duplicates present in that table for which this error has been raised.

I've checked the index and related columns as well. NO DATA is there.

So NO CHANCE for unique constraint violation. Please suggest me something. Is there anything I've missed?

Okay, please do have a look at this query.

SELECT * FROM ORDER_OCC_REQUISITION_X_REF WHERE LAB_ORDER_OCC_TEST_ID IN(SELECT LAB_ORDER_OCC_TEST_ID FROM LAB_ORDER_OCC_TEST WHERE LAB_ORDER_OCC_ID = 7944858);
no rows selected

Now when I'm trying to insert one row inside this table I'm getting this error, as you are seeing no records for this occurrence_id.

SELECT * FROM USER_INDEXES WHERE INDEX_NAME = 'ORD_OCC_REQ_UQ_TEST_IX_04';
--ORDER_OCC_REQUISITION_X_REF (Table name)
--MERGE_DT, LAB_ORDER_OCC_TEST_ID, TEST_ID, ACTIVE_YN (columns for the index 'ORD_OCC_REQ_UQ_TEST_IX_04')

As you can see there is no data then this error should not be raised. please suggest something.

Update procedure.

/*******************************************************************************************************************

* Name : UPDATE_REQUISITION_X_REF

* Description : This Procedure update ORDER_OCC_REQUISITION_X_REF table with requisition_id

* that was generated due to merge process.

* In parameters : IN_merge_id NUMBER The order_ref_no of the orders to be merged (comma seperated)

***********************************************************************************************************************/

PROCEDURE UPDATE_REQUISITION_X_REF ( IN_merge_id IN TT_ORD_REQUISITION_WORK_AREA.merge_id%TYPE)

CC_PROCEDURE_NAME CONSTANT DEBUG_LOG.procedure_Name%TYPE :='UPDATE_REQUISITION_X_REF';

CD_SYSDATE CONSTANT DATE := SYSDATE;

BEGIN

Spl_Spn_Error_Logging_Spk.INFO_PROC

(ic_package_Name => CC_PACKAGE_NAME

,ic_procedure_Name => CC_PROCEDURE_NAME

,in_batch_id => vn_batch_id

,ic_message_text => 'Start of Procedure.');

Spl_Spn_Error_Logging_Spk.DEBUG_PROC

(ic_package_Name => CC_PACKAGE_NAME

,ic_procedure_Name => CC_PROCEDURE_NAME

,in_batch_id => vn_batch_id

,ic_message_text => 'Entering procedure with IN_merge_id: '

|| IN_merge_id);

UPDATE ORDER_OCC_REQUISITION_X_REF

SET active_yn = CC_REQUISITION_ACTIVE_N

WHERE requisition_hdr_id IN (SELECT requisition_hdr_id

FROM TT_ORD_REQUISITION_WORK_AREA

WHERE merge_id = IN_merge_id)

AND active_yn =CC_REQUISITION_ACTIVE_Y;

INSERT INTO ORDER_OCC_REQUISITION_X_REF

(ord_occ_req_xref_id

, requisition_hdr_id

, lab_order_occ_id

, merge_dt

, draw_dt

, active_yn

, requisition_no

, lab_order_occ_test_id

, test_id

, order_duration_type

, frequency

, order_ref_no

, schedule_id)

SELECT ORD_OCC_REQ_XREF_ID.NEXTVAL

, requisition_hdr_id

, lab_ord_occ_id

, cd_sysdate

, draw_dt

, cc_requisition_active_Y

, current_requisition_no

, lab_ord_occ_test_id

, test_id

, order_duration_type

, frequency

, lab_ord_occ_ref_no

, schedule_id

FROM TT_ORD_REQUISITION_WORK_AREA wa

WHERE merge_id =IN_merge_id;

Spl_Spn_Error_Logging_Spk.DEBUG_PROC

(ic_package_Name => CC_PACKAGE_NAME

,ic_procedure_Name => CC_PROCEDURE_NAME

,in_batch_id => vn_batch_id

,ic_message_text => SQL%rowcount ||' Inserted into ORDER_OCC_REQUISITION_X_REF ');

FOR vn_requisition_id IN ( SELECT DISTINCT requisition_hdr_id

FROM TT_ORD_REQUISITION_WORK_AREA

WHERE merge_id =IN_merge_id)

LOOP

GENERATE_ACCESSION_NUMBER (IN_requisition_id => vn_requisition_id.requisition_hdr_id );

Spl_Spn_Error_Logging_Spk.DEBUG_PROC

(ic_package_Name => CC_PACKAGE_NAME

,ic_procedure_Name => CC_PROCEDURE_NAME

,in_batch_id => vn_batch_id

,ic_message_text => 'Updated ORDER_REQUISITION_DETAIL');

UPDATE ORDER_REQUISITION_DETAIL ord

SET accession_no = (SELECT DISTINCT accession_no

FROM ORDER_OCC_REQUISITION_X_REF xref

WHERE xref.lab_order_occ_test_id = ord.lab_ord_occ_test_id

AND xref.test_id = ord.test_id

AND xref.REQUISITION_HDR_ID = vn_requisition_id.REQUISITION_HDR_ID

AND xref.active_yn = CC_REQUISITION_ACTIVE_Y

AND ROWNUM = 1)

WHERE ord.REQUISITION_HDR_ID = vn_requisition_id.REQUISITION_HDR_ID;

Spl_Spn_Error_Logging_Spk.DEBUG_PROC

(ic_package_Name => CC_PACKAGE_NAME

,ic_procedure_Name => CC_PROCEDURE_NAME

,in_batch_id => vn_batch_id

,ic_message_text => SQL%rowcount

||' Updated with Accession NUMBER in ORDER_REQUISITION_DETAIL');

END LOOP;

Spl_Spn_Error_Logging_Spk.INFO_PROC

(ic_package_Name => CC_PACKAGE_NAME

,ic_procedure_Name => CC_PROCEDURE_NAME

,in_batch_id => vn_batch_id

,ic_message_text => 'End of Procedure.');

EXCEPTION

WHEN OTHERS THEN

Spl_Spn_Error_Logging_Spk.FATAL_PROC

(ic_package_Name => CC_PACKAGE_NAME,

ic_procedure_Name => CC_PROCEDURE_NAME,

in_batch_id => vn_batch_id,

ic_message_text => SUBSTR (SQLERRM,1,4000));

RAISE_APPLICATION_ERROR(-20014,SUBSTR (SQLERRM,1,4000)

||' Error in UPDATE_REQUISITION_X_REF'

||CHR(13)||dbms_utility.format_error_stack);

END UPDATE_REQUISITION_X_REF;

Table Script.

CREATE TABLE ORDER_OCC_REQUISITION_X_REF

(

ORD_OCC_REQ_XREF_ID NUMBER(10,0) NOT NULL ENABLE,

REQUISITION_HDR_ID NUMBER(10,0),

LAB_ORDER_OCC_ID NUMBER(10,0),

MERGE_DT DATE,

DRAW_DT DATE,

ACTIVE_YN CHAR(1 BYTE),

REQUISITION_NO VARCHAR2(7 BYTE),

ACCESSION_NO VARCHAR2(20 BYTE),

LAB_ORDER_OCC_TEST_ID NUMBER(10,0),

TEST_ID NUMBER(10,0),

ORDER_DURATION_TYPE CHAR(1 BYTE),

FREQUENCY VARCHAR2(4 BYTE),

ORDER_REF_NO NUMBER(20,0),

SCHEDULE_ID NUMBER(10,0),

CONSTRAINT ORDER_OCC_REQUISITION_X_REF_PK PRIMARY KEY (ORD_OCC_REQ_XREF_ID) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE ABCD_INDEX ENABLE,

CONSTRAINT ORD_OCC_REQ_X_REF_CK_ACTIVE CHECK (Active_YN IN('Y','N')) ENABLE,

CONSTRAINT ORD_OCC_REQ_FK_REQ_HDR FOREIGN KEY (REQUISITION_HDR_ID) REFERENCES ORDER_REQUISITION_HEADER (REQUISITION_HDR_ID) ENABLE

);

COMMENT ON COLUMN ORDER_OCC_REQUISITION_X_REF.ORD_OCC_REQ_XREF_ID

'The system generated id for uniquely identifier for the historic information of merged test';

COMMENT ON COLUMN ORDER_OCC_REQUISITION_X_REF.REQUISITION_HDR_ID

'The id refer to requistion which is merged test value';

COMMENT ON COLUMN ORDER_OCC_REQUISITION_X_REF.LAB_ORDER_OCC_ID

'The id refer to the occurrence which is merged';

COMMENT ON COLUMN ORDER_OCC_REQUISITION_X_REF.MERGE_DT

'The date on which the merge process took place';

COMMENT ON COLUMN ORDER_OCC_REQUISITION_X_REF.DRAW_DT

'The occurrence date on which the draws will be done for a test';

COMMENT ON COLUMN ORDER_OCC_REQUISITION_X_REF.ACTIVE_YN

'The status of the requisiton_number. Can be A-Active N- Not active';

COMMENT ON COLUMN ORDER_OCC_REQUISITION_X_REF.REQUISITION_NO

'The requisition number for the test';

COMMENT ON COLUMN ORDER_OCC_REQUISITION_X_REF.ACCESSION_NO

'The accession number for the tests. Used for labeling';

COMMENT ON COLUMN ORDER_OCC_REQUISITION_X_REF.LAB_ORDER_OCC_TEST_ID

'Referential to the order occurrence test';

COMMENT ON COLUMN ORDER_OCC_REQUISITION_X_REF.TEST_ID

'Referential to the Test';

COMMENT ON COLUMN ORDER_OCC_REQUISITION_X_REF.ORDER_DURATION_TYPE

'This would indicate the duration type of the order S -- Short term order L-- Long term order A -- Adhoc order E - Environmental order';

COMMENT ON COLUMN ORDER_OCC_REQUISITION_X_REF.FREQUENCY

'The frequency of the test';

COMMENT ON COLUMN ORDER_OCC_REQUISITION_X_REF.ORDER_REF_NO

'Occurrence reference number for the merged test occurrence';

COMMENT ON COLUMN ORDER_OCC_REQUISITION_X_REF.SCHEDULE_ID

'The frequency schedule for the test';

COMMENT ON TABLE ORDER_OCC_REQUISITION_X_REF

'Holds the historic relation between lab_order_occ_id and requisition number';

CREATE INDEX ORDER_OCC_REQ_X_REF_IX_01 ON ORDER_OCC_REQUISITION_X_REF

(

LAB_ORDER_OCC_ID,

LAB_ORDER_OCC_TEST_ID

)

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE

(

INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT

)

TABLESPACE ABCD_INDEX ;

CREATE INDEX ORDER_OCC_REQ_X_REF_IX_02 ON ORDER_OCC_REQUISITION_X_REF

(

REQUISITION_HDR_ID

)

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE

(

INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT

)

TABLESPACE ABCD_INDEX ;

CREATE UNIQUE INDEX ORD_OCC_REQ_UQ_TEST_IX_04 ON ORDER_OCC_REQUISITION_X_REF

(

MERGE_DT, LAB_ORDER_OCC_TEST_ID, TEST_ID, ACTIVE_YN

)

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE

(

INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT

)

TABLESPACE ABCD_INDEX ;

Regards,

BS2012.

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 1 2013

Added on Oct 4 2013

8 comments

3,049 views