Error: "java.sql.SQLSyntaxErrorException: ORA-00947: not enough values" When Running An ODI ETL UDF Scenario For A P6 Datasource

(Doc ID 2337292.1)

Last updated on JANUARY 02, 2018

Applies to:

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

Symptoms

When attempting to run the daily ETL against a P6 datasource, the following errors occur in the log causing the ETL execution to fail:

ERROR #1
-----------------------
  [ERROR] Load resource UDF, duration = 1 seconds
  [ERROR] PROC_LOAD_RESOURCE_UDF
  [ERROR] 0 \-\ Load Resource Udf
  [ERROR] 1 /-/ Procedure - Load Resource Udf - Load Resource Udf Dim
ODI-1228: Task Procedure-Load Resource Udf-Load Resource Udf Dim fails on the target connection PRIMAVERA_ANALYTICS_TGT_DS.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00947: not enough values
ORA-06512: at "STARUSER.DYNAMIC_UDFS", line 218
ORA-06512: at "STARUSER.DYNAMIC_UDFS", line 238
ORA-06512: at "STARUSER.DYNAMIC_UDFS", line 256
ORA-06512: at line 2
 ...
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:466)



ERROR #2
-----------------------
 [ERROR] Load assignment UDF, duration = 2 seconds
 [ERROR] PROC_LOAD_ASSIGNMENT_UDF
 [ERROR] 0 \-\ Load Assignment Udf
 [ERROR] 1 /-/ Procedure - Load Assignment Udf - Load Assignment Udf Dim
ODI-1228: Task Procedure-Load Assignment Udf-Load Assignment Udf Dim fails on the target connection PRIMAVERA_ANALYTICS_TGT_DS.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00947: not enough values
ORA-06512: at "STARUSER.DYNAMIC_UDFS", line 218
ORA-06512: at "STARUSER.DYNAMIC_UDFS", line 238
ORA-06512: at "STARUSER.DYNAMIC_UDFS", line 263
ORA-06512: at line 2
 ...
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:466)


ERROR #3
-----------------------
 [ERROR] Load project UDF, duration = 3 seconds
 [ERROR] PROC_LOAD_PROJECT_UDF
 [ERROR] 0 \-\ Load Project Udf
 [ERROR] 1 /-/ Procedure - Load Project Udf - Load Project Udf Dim
ODI-1228: Task Procedure-Load Project Udf-Load Project Udf Dim fails on the target connection PRIMAVERA_ANALYTICS_TGT_DS.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00947: not enough values
ORA-06512: at "STARUSER.DYNAMIC_UDFS", line 218
ORA-06512: at "STARUSER.DYNAMIC_UDFS", line 238
ORA-06512: at "STARUSER.DYNAMIC_UDFS", line 252
ORA-06512: at line 2
 ...
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:466)



ERROR #4
-----------------------
 [ERROR] Load activity UDF, duration = 6 seconds
 [ERROR] PROC_LOAD_ACTIVITY_UDF
 [ERROR] PROC_LOAD_ACTIVITY_UDF_FACT
 [ERROR] 0 \-\ Load Activity Udf Fact
 [ERROR] 1 /-/ Procedure - Load Activity Udf Fact - Load Activity Udf Fact
ODI-1228: Task Procedure-Load Activity Udf Fact-Load Activity Udf Fact fails on the target connection PRIMAVERA_ANALYTICS_TGT_DS.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00947: not enough values
ORA-06512: at "STARUSER.DYNAMIC_UDFS", line 155
ORA-06512: at "STARUSER.DYNAMIC_UDFS", line 176
ORA-06512: at "STARUSER.DYNAMIC_UDFS", line 284
ORA-06512: at line 2
 ...
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:466)


ERROR #5
-----------------------
 [ERROR] Load WBS UDF, duration = 4 seconds
 [ERROR] PROC_LOAD_WBS_UDF
 [ERROR] 0 \-\ Load WBS Udf
 [DONE] 1 /-/ Procedure - Load WBS Udf - Load WBS Udf Dim
 [ERROR] 2 /-/ Procedure - Load WBS Udf - Load WBS Udf Fact
ODI-1228: Task Procedure-Load WBS Udf-Load WBS Udf Fact fails on the target connection PRIMAVERA_ANALYTICS_TGT_DS.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00947: not enough values
ORA-06512: at "STARUSER.DYNAMIC_UDFS", line 155
ORA-06512: at "STARUSER.DYNAMIC_UDFS", line 176
ORA-06512: at "STARUSER.DYNAMIC_UDFS", line 346
ORA-06512: at line 2
 ...
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:466)

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Login to the Data Warehouse Web ETL application (http://host:port//p6rdb)
2. Select staretl1
3 Select Run ETL
4. On the status tab, note the failures of the ETL run once it completes

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot run a successful ETL to populate data warehouse from a P6 datasource causing Analytics to report against incorrect/invalid data.

Changes

 

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