ETL process p_arc_01 completes with error "ORA-00001: unique constraint ..."
- Article Type: General
- Product: Aleph
- Product Version: 20, 21, 22, 23
Problem Symptoms:
* ETL process p_arc_01 contains errors in log $data_scratch/p_arc_01.log
PROCESS_CODE TASK_CODE ERROR_MESSAGE
833 792 ORA-00001: unique constraint (.) violated
[...]
Cause:
Duplicate lines in table ./xxx50/tab/tab40.lng
Resolution:
Process number 792 deals with the upload of collection information, original information from Aleph table ./xxx50/tab/tab40.lng
* Remove duplicate lines from ./xxx50/tab/tab40.lng
* The changes will be reflected after the next ETL process
Example
Below, the collection EBSCO with sublibrary 'ABC' in column 2 is listed twice in the table, thus creating duplicate keys in ARC.
./xxx50/tab/tab40.eng
aleph@alephprod-app-1(a20_1) SUT50> grep EBSCO tab40.eng
EBSCO ABC L Online book - ebook EBSCO
EBSCO ABC L Online book - ebook EBSCO
**ALSO**
We have found that lines which are "logically" duplicated (via wildcards), such as the following, can cause these messages:
REFPE BLSH L Référence Périodiques
REFPE ##### L Référence Périodiques
and:
RESER BLSH L Réserve
RESER ##### L Réserve
(In such cases the first line is extraneous and can be commented out since it is covered by the ##### line.)
Additional Information
The "unique constraint violated" errors are coming from the process of copying the extracted values from Aleph into the "STG_" tables. The extract process gets information from Aleph libraries as well as Aleph tables and loads it into ARC tables prefixed with "MRR_" (for mirror). These tables allow for duplicate keys to be inserted, as they are never used for reports. However, when the records in the MRR tables are loaded into the STG tables, there can be no duplicates. So, for each duplicate value in the MRR tables, there will be an error message that says that a unique constraint has been violated. In other words, the records must all have unique keys, but the process has tried to insert a duplicate key and that is not allowed.
Category: Cataloging (500)
- Article last edited: 10/8/2013