We are in 14.2. When we try to run the Load Datawarehouse Job as a full load, it fails within few seconds with the following error. It looks like there is no table name passed as a first parameter.
An error occurred executing this job entry :Couldn't execute SQL: BEGINCMN_DISABLE_CONSTRAINTS_SP(NULL,'PK');END;
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-01735: invalid ALTER TABLE optionORA-06512: at "PPM_DWH.CMN_DISABLE_CONSTRAINTS_SP", line 25ORA-06512: at line 2
Can somebody please help?
I see, it does try to run it for NULL.
Please connect as PPM_DWH and check your user_constraints:
select * from user_constraints
Make sure you don't have any weird constraint that is not associated to any table (column TABLE_NAME ). If you have any:
If you don't or that doesn't help, please do the following:
Hope this helps -Nika
Thank you so much for your reply. Your response really helped!
Yes, it was having weird constraint names and I removed them with the help of our DBA.
Then I tried running a full load; it did not fail with the same error but with a different error now.
ERROR 2017-06-29 07:17:29,603 [dwh_etl_interface_dim - User Defined Java Class] dwh.event User Defined Java Class - java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING DIMENSION JOB EXECUTION FOR THE TABLE - DWH_INV_STATUS_REPORT
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "SR_COST_EFFORT_STATUS_KEY": invalid identifier
I think this is somethinng related to a system field - Cost and Effort Status in the system sub object - Status Report but I could not further analyse or able to fix this issue.
Can you please advise?
Great to hear this issue was resolved!
Please do the following to resolve the new error on Status Report:
See if that helps. If it doesn't, uncheck and check the field Cost and Effort Status for Data Warehouse and try running the Full Load again.
You are really rocking!
Issue fixed. I followed those 5 steps and that cleared all the issues.
Thanks a ton!