Skip to main content
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    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

             833       792 ORA-00001: unique constraint (.) violated

    Duplicate lines in table ./xxx50/tab/tab40.lng

    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

    Below, the collection EBSCO with sublibrary 'ABC' in column 2 is listed twice in the table, thus creating duplicate keys in ARC.

    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


    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 

    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