Error: "ORA-01427 single-row subquery returns more than one row" During ETL Run (Doc ID 1958094.1)

Last updated on MARCH 22, 2016

Applies to:

Primavera Analytics - Version R1 and later
Primavera Data Warehouse - Version R2 and later
Primavera P6 Enterprise Project Portfolio Management - Version 6.2.1 and later
Information in this document applies to any platform.

Symptoms

ETL SYMPTOMS

An ORA-01427 single-row subquery returns more than one row  error occurs during the ETL process.

Depending upon the version of Reporting Database being used, the failure will identify EITHER the update_project_wbs_ids.sql  OR star_etl_update_table_both_update_project_eps_wid.sql as the offending scripts.

Example of Error  (For Reporting Database 2.2 or later)

Update Project WBS Object IDs (update_project_wbs_ids.sql)
staretl [DEBUG] (Message) - SQL Plus(4) : [-s] [STAR4547AF01/{db.star.application.password}@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=HOSTNAME)(PORT=PORTNUMBER))(CONNECT_DATA=(SERVICE_NAME=SERVICENAME)))] [@run$script.sql] [SCRIPTNAME:update_project_wbs_ids.sql]
staretl [INFO] (Message) - Exception: Error during update for table W_PROJECT_D and W_PROJECT_HD:
staretl [INFO] (Message) - ORA-01427: single-ro
staretl [INFO] (Message) - (100,W_PROJECT_D,W_PROJECT_D,n/a,n/a)
staretl [INFO] (Message) - declare
staretl [INFO] (Message) - *
staretl [INFO] (Message) - ERROR at line 1:
staretl [INFO] (Message) - ORA-01427: single-row subquery returns more than one row

Example of Error   (For Reporting Database R2 version)

incremental [DEBUG] (Message) - SQL Plus(4) : [-s] <'Staruser' account connection info> [SCRIPTNAME:star_etl_update_table_both_update_project_eps_wid.sql]
incremental [INFO] (Message) - set (eps_wid, eps_object_id) =(select e.row_wid, w.wbs_object_id
incremental [INFO] (Message) - *
incremental [INFO] (Message) - ERROR at line 2:
incremental [INFO] (Message) - ORA-01427: single-row subquery returns more than one row

 

PROJECT MANAGEMENT SYMPTOMS

Even when a customer is NOT using the Reporting Database/Data Warehouse products, the projwbs table may contain some invalid rows (where more than 1 row has the proj_node_flag = 'Y')

In these situations, the Project Management steps shown in this solution can be used to correct the problem.

Possible Errors returned:

In PM.exe during project open:  Event code SRMT1-2331

 

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