Skip to main content
ExLibris

  • Subscribe by RSS
    • Aleph
    Ex Libris Knowledge Center

    ETL process p_arc_01 completes with error "ORA-00001: unique constraint ..."

    1. Last updated
    2. Save as PDF
    • 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