My Oracle Support Banner

Error: "ORA-01427: single-row subquery returns more than one row" In UPDATE_PROJECT_PKG When Executing Data Warehouse P6 EPPM ETL After Moving A Published Project WBS As A Child To A New, Unpublished WBS (Doc ID 2431391.1)

Last updated on FEBRUARY 03, 2019

Applies to:

Primavera Data Warehouse - Version 16.2.3.0 and later
Information in this document applies to any platform.

Symptoms

When a published project, which contains parent-child wbs hiearchy, has a new WBS added and one of the previously published child WBS elements added to the new WBS, the following error will occur executing the "Update Project WBS Object IDs (update_project_wbs_ids.sql)" Data Warehouse ETL step if the project is not republished first:

ERROR
------
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "STARUSER.UPDATE_PROJECT_PKG", line 75
ORA-06512: at line 4

 

ADDITIONAL INFORMATION
The failure will occur while executing the "update_project_wbs_ids.sql" script.

Example failure message in the ETL log, however the step number can vary between Data Warehouse release versions:

TIMESTAMP staretl INFO (Progress) - Step 30/51 Part 1/2 - TIMESTAMP UpdateProjectWBSObjectIds -- Update Project WBS Object IDs (update_project_wbs_ids.sql)
TIMESTAMP  staretl INFO (Message) - Connecting to database...
TIMESTAMP  staretl INFO (Message) - === Step 30, Part 1 ====================================================
TIMESTAMP  staretl INFO (Message) - === Update Project WBS Object IDs (update_project_wbs_ids.sql)
TIMESTAMP  staretl INFO (Message) - Exception: Error during update for table W_PROJECT_D and W_PROJECT_HD:
TIMESTAMP  staretl INFO (Message) - ORA-01427: single-row subquery returns more than one row
TIMESTAMP  staretl INFO (Message) - (100,W_PROJECT_D,W_PROJECT_D,n/a,n/a)
TIMESTAMP  staretl INFO (Message) - DECLARE
TIMESTAMP  staretl INFO (Message) - *
TIMESTAMP  staretl INFO (Message) - ERROR at line 1:
TIMESTAMP  staretl INFO (Message) - ORA-01427: single-row subquery returns more than one row
TIMESTAMP  staretl INFO (Message) - ORA-06512: at "STARUSER.UPDATE_PROJECT_PKG", line 75
TIMESTAMP  staretl INFO (Message) - ORA-06512: at line 4
TIMESTAMP  staretl INFO (Progress) - Step 30/51 Part 1/2 - FAILED (147) (0 hours, 0 minutes, 1 seconds, 430 milliseconds)


STEPS
The issue can be reproduced at will with the following steps:

  1. Create new project
  2. Create WBS Hiearchy which mimics the following:
    1. Create a WBS called P1 with project as parent
    2. Create a WBS called P2 with project as parent
    3. Create a WBS called P3 with project as parent
    4. Create a WBS called 1.1 with P1 as parent
    5. Create a WBS called 2.1 with P2 as parent
    6. Create a WBS called 3.1 with P3 as parent
  3. Add an activity to the following WBS's:
    • 1.1
    • 2.1
    • 3.1
  4. Publish Project
  5. Execute 'Publish Security' global scheduled service
  6. Create a new WBS called P4 with project as parent
  7. Cut/Paste WBS 1.1 to WBS P4
    • This will make WBS P4 the child of 1.1 instead of WBS P1
  8. Execute the Data Warehouse ETL
  9. Note the error which occurs executing the "Update Project WBS Object IDs (update_project_wbs_ids.sql)" step:
    • ORA-01427: single-row subquery returns more than one row
      ORA-06512: at "STARUSER.UPDATE_PROJECT_PKG", line 75
      ORA-06512: at line 4

 

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.