Issue/Introduction:
Issue:
Load Data Warehouse job will fail when on a single object the combined length of the IDs of all custom attribute that are included in the Data Warehouse exceed 4000 characters. This issue is due to the limitation from Oracle on its 11G and lower versions, the limit will be at 8000 characters starting from Oracle 12C.
Error shown in the bg log:
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 11
The error message shown on the UI is generic, it should be more descriptive from the UI.
MSSQL also faces the same issue.
For MSSQL the following error message is shown in the log files.
no Records to Process? - An error occurred executing this job entry :
Couldn't execute SQL: DECLARE @V_SQL_TEXT nvarchar(max);
BEGIN
SET @V_SQL_TEXT = 'select <LONG LIST OF CUSTOM ATTRIBUTES BEING FLAGGED FOR DATA WAREHOUSE AND BEING CUT OFF IN THE JOB'S LOG>
User Defined Java Class - Unexpected error
User Defined Java Class - java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING DIMENSION JOB EXECUTION FOR THE TABLE - DWH_INV_PROJECT
at Processor.processRow(Processor.java:64)
at org.pentaho.di.trans.steps.userdefinedjavaclass.UserDefinedJavaClass.processRow(UserDefinedJavaClass.java:1181)
at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
at java.lang.Thread.run(Thread.java:745)
Steps to Reproduce:
- Add many custom attributes to the Investment object, and give them lengthy IDs such that the combined length of all their IDs is over 4000 characters
- Check the 'Include in the Data Warehouse' box for all of the above attributes
- Run the 'Load Data Warehouse' (LDWH) job
Expected Result: The job fails with an error stating the cause being with the combined length of custom attribute IDs on the object
Actual Result: The job fails with a generic error that that the job has failed and user should check the log for details
Environment:
Applies to all supported PAS environments for specified releases.
Cause:
Caused by CLRT-78443
Resolution:
Resolved in CA PPM 14.2 Generic Patch #7.
Resolved in CA PPM 14.3
Workaround:
Un-check some of the custom attributes for inclusion to the Data Warehouse and run the LDWH job again
Additional Information:
Reference TEC1398937 : PPM 14.2 Generic Patch available to resolve reported issues
Reference CA PPM Resolved Defects Index for CA PPM 14.3