Error: "ORA-00001: unique constraint (STARUSER.W_PROJECT_SECURITY_S_KEY1) violated" While Processing The Project Security Data

(Doc ID 1996081.1)

Last updated on SEPTEMBER 20, 2017

Applies to:

Primavera Data Warehouse - Version 15.1.0.0 and later
Primavera Analytics Cloud Service - Version 16.1.0.0 and later
Information in this document applies to any platform.

Symptoms

While running the ETL for Data Warehouse, the following error occurs while processing project security into W_PROJECT_SECURITY_S:

ERROR
-----------------------
When running starETL, the following error occurs during the w_project_security_s processing:

...
TIMESTAMP [main] INFO ETL.Progress - Step 28/56 Part 1/1 - [TIMESTAMP] SecurityLoad -- Load Security Tables (sec_load)
TIMESTAMP [main] INFO ETL.Message - Connecting to database...
TIMESTAMP [main] INFO ETL.Message - === Step 28, Part 1 ====================================================
TIMESTAMP [main] INFO ETL.Message - === Load Security Tables (sec_load)
TIMESTAMP [pool-3-thread-1] INFO ETL.Message - Adding sql script unifier_company_security.sql to the job queue
TIMESTAMP [pool-3-thread-1] INFO ETL.Progress - Starting Script unifier_company_security.sql
TIMESTAMP [Thread-1] INFO ETL.Message - LoadSecurity Insert Time for 0 rows on table W_PROJECT_SECURITY_S is .001 mins
TIMESTAMP [Thread-1] INFO ETL.Message - (.08s) at rate 0/s
TIMESTAMP [Thread-1] INFO ETL.Message - Complete load on W_PROJECT_SECURITY_S for table W_PROJECT_SECURITY_S rows=0
TIMESTAMP [Thread-1] INFO ETL.Message - Processed 0 rows in .083/s at rate of 0/s
TIMESTAMP [Thread-1] INFO ETL.Message - PL/SQL procedure successfully completed.
TIMESTAMP [pool-3-thread-1] INFO ETL.Progress - Finished Script unifier_company_security.sql
TIMESTAMP [pool-4-thread-1] INFO ETL.Message - Adding sql script project_security.sql to the job queue
TIMESTAMP [pool-4-thread-1] INFO ETL.Progress - Starting Script project_security.sql
TIMESTAMP [pool-4-thread-2] INFO ETL.Message - Adding sql script resource_security.sql to the job queue
TIMESTAMP [pool-4-thread-2] INFO ETL.Progress - Starting Script resource_security.sql
TIMESTAMP [Thread-5] INFO ETL.Message - LoadSecurity Insert Time for 702 rows ON table w_resource_security_s is .002
TIMESTAMP [Thread-5] INFO ETL.Message - mins (.09s) at rate 7800/s
TIMESTAMP [Thread-5] INFO ETL.Message - Complete load on w_resource_security_s for table w_resource_security_s rows=702
TIMESTAMP [Thread-5] INFO ETL.Message - Processed 702 rows in .091/s at rate of 7714.286/s
TIMESTAMP [Thread-5] INFO ETL.Message - PL/SQL procedure successfully completed.
TIMESTAMP [pool-4-thread-2] INFO ETL.Progress - Finished Script resource_security.sql
TIMESTAMP [Thread-3] INFO ETL.Message - ORA-06512: at "STARUSER.SEC_LOAD_PKG", line 35
TIMESTAMP [Thread-3] INFO ETL.Message - Exception: Error during load for table W_PROJECT_SECURITY_S: ORA-00001: unique
TIMESTAMP [Thread-3] INFO ETL.Message - constraint (
TIMESTAMP [Thread-3] INFO ETL.Message - (100,W_PROJECT_SECURITY_SS,W_PROJECT_SECURITY_S,n/a,n/a)
TIMESTAMP [Thread-3] INFO ETL.Message - declare
TIMESTAMP [Thread-3] INFO ETL.Message - *
TIMESTAMP [Thread-3] INFO ETL.Message - ERROR at line 1:
TIMESTAMP [Thread-3] INFO ETL.Message - ORA-00001: unique constraint (STARUSER.W_PROJECT_SECURITY_S_KEY1) violated
TIMESTAMP [Thread-3] INFO ETL.Message - ORA-06512: at "STARUSER.SEC_LOAD_PKG", line 155
TIMESTAMP [Thread-3] INFO ETL.Message - ORA-06512: at line 4
TIMESTAMP [pool-4-thread-1] INFO ETL.Progress - Finished Script project_security.sql
TIMESTAMP [main] INFO ETL.Progress - Step 28/56 Part 1/1 - DONE (0 hours, 0 minutes, 1 seconds, 543 milliseconds)
...

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run the ETL
2. Note the reported issue which occurs during the step.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms