Hi Suman,
After I executed the command also, DWH jib is failing.
Here is the log from bg-dwh.log
Couldn't execute SQL: DECLARE
V_SQL_TEXT CLOB;
V_SQLCODE NUMBER;
V_SQLERRM VARCHAR2(4000);
TYPE DIM_REC IS RECORD (AS_OF_DATE DATE, OBJ_STAKEHOLDER1_CAPTION VARCHAR2(2100), BUSINESS_OWNER_KEY NUMBER(32), ESTIMATING_FACTOR_1 NUMBER(32), ESTIMATING_FACTOR_2 NUMBER(32), EV_CALC_METHOD_KEY NUMBER(32), EV_PERIOD_ID_CAPTION VARCHAR2(2100), EV_RPT_PERIOD_KEY NUMBER(32), OBJ_STAKEHOLDER3_CAPTION VARCHAR2(2100), FINANCE_GOVERNANCE_KEY NUMBER(32), IDEA_PRJ_CATEGORY_KEY VARCHAR2(255), IDEA_PRJ_TYPE_KEY VARCHAR2(255), INVESTMENT_KEY NUMBER(32), IS_FINISH_IMPOSED NUMBER(32), IS_PROGRAM NUMBER(32), IS_REQ_APPROVAL_REQUIRED NUMBER(32), IS_START_IMPOSED NUMBER(32), OBJECTIVE VARCHAR2(4000), OBJ_STAKEHOLDER2_CAPTION VARCHAR2(2100), PROJECT_MGMT_OFFICE_KEY NUMBER(32), RISK_FLEXIBILITY_KEY NUMBER(32), RISK_FUNDING_KEY NUMBER(32), RISK_HUMAN_INTERFACE_KEY NUMBER(32), RISK_IMPLEMENTATION_KEY NUMBER(32), RISK_INTERDEPENDENCY_KEY NUMBER(32), RISK_OBJECTIVES_KEY NUMBER(32), RISK_ORG_CULTURE_KEY NUMBER(32), RISK_RESOURCE_AVAIL_KEY NUMBER(32), RISK_SPONSORSHIP_KEY NUMBER(32), RISK_SUPPORTABILITY_KEY NUMBER(32), RISK_TECHNICAL_KEY NUMBER(32), SPONSORED_BY VARCHAR2(96), STATUS_REPORTING_KEY VARCHAR2(255), Z_ACHIVED_DELIVERAB VARCHAR2(4000), Z_ANNUAL_SALES_VOL NUMBER(32), Z_ANNUAL_SALES_VOLY1 NUMBER(32), Z_ANNUAL_SALES_VOLY2 NUMBER(32), Z_ANNUAL_SALES_VOLY3 NUMBER(32), Z_ANN_TOV_CALC NUMBER(32, 6), Z_ANN_VOL NUMBER(32), Z_AUT_ITEM_NUMBER VARCHAR2(60), Z_BALANCE_ATTACK_KEY VARCHAR2(255), Z_BOOKED_BUSINESS_KEY VARCHAR2(255), Z_COMMENT VARCHAR2(1200), Z_COMPOUND_KEY VARCHAR2(255), Z_CONGLOMERATE VARCHAR2(90), Z_CUSTOMER_NAME_IN VARCHAR2(150), Z_CUST_CLASSIFICAT VARCHAR2(90), Z_CUST_NAME_NEW VARCHAR2(60), Z_CUST_PART_NO VARCHAR2(120), Z_ENG_ACTION_REQUEST VARCHAR2(4000), Z_FIRST_SUPPLY_DATE DATE, Z_GOODSCOST NUMBER(32, 6), Z_MULTIPART NUMBER(32), Z_PARTS_APPLICATION_KEY VARCHAR2(255), Z_PRJ_ESTCOMPLDATE DATE, Z_PROJECT_TYPE_KEY VARCHAR2(255), Z_PROPOSAL_DATE_INDI DATE, Z_TF_ACTIVITIES_NEXT VARCHAR2(4000), Z_TF_ASSUMP_CONS_RI VARCHAR2(4000), Z_TF_BDG_CMT VARCHAR2(4000), Z_TF_BDG_PL NUMBER(32, 6), Z_TF_BDG_PL_COM VARCHAR2(4000), Z_TF_BUSINESS_MGR_CAPTION VARCHAR2(2100), Z_TF_BUSINESS_MGR_KEY NUMBER(32), Z_TF_CUST_DRWG_NO VARCHAR2(120), Z_TF_EXE_SUMM VARCHAR2(4000), Z_TF_FEB VARCHAR2(4000), Z_TF_FLEX_COMMENTS VARCHAR2(690), Z_TF_HIGH_LOW VARCHAR2(4000), Z_TF_IMPL_COMMENTS VARCHAR2(690), Z_TF_JAN VARCHAR2(4000), Z_TF_KEY_LEARN VARCHAR2(4000), Z_TF_OBJ_COMMENTS VARCHAR2(690), Z_TF_PRJ_BOUNDARIS VARCHAR2(4000), Z_TF_PRJ_DELIV VARCHAR2(4000), Z_TF_PRJ_HEALTH_STAT_KEY VARCHAR2(255), Z_TF_PRJ_LVL_KEY VARCHAR2(255), Z_TF_PRJ_OBJECTIVE VARCHAR2(4000), Z_TF_PRJ_PHASE_KEY VARCHAR2(255), Z_TF_PRJ_STATUS_KEY VARCHAR2(255), Z_TF_PROD_GRP_KEY VARCHAR2(255), Z_TF_PROJ_SPONS_CAPTION VARCHAR2(2100), Z_TF_PROJ_SPONS_KEY NUMBER(32), Z_TF_STAKEHOLDER_AD VARCHAR2(4000), Z_TF_TIM_PL NUMBER(32, 6), Z_TF_TSS_DRWG_NO VARCHAR2(120), Z_TF_TSS_PART_NO VARCHAR2(600), Z_TSS_QUOTATION_NO VARCHAR2(90), dw_updated_date date);
TYPE ARRAY IS TABLE OF DIM_REC;
l_data ARRAY;
l_cursor SYS_REFCURSOR;
BEGIN
CMN_SAVEDROP_CREATE_INDEXES_SP('DWH_INV_PROJECT','SAVE_DROP');
V_SQL_TEXT := 'select AS_OF_DATE, OBJ_STAKEHOLDER1_CAPTION, BUSINESS_OWNER_KEY, ESTIMATING_FACTOR_1, ESTIMATING_FACTOR_2, EV_CALC_METHOD_KEY, EV_PERIOD_ID_CAPTION, EV_RPT_PERIOD_KEY, OBJ_STAKEHOLDER3_CAPTION, FINANCE_GOVERNANCE_KEY, IDEA_PRJ_CATEGORY_KEY, IDEA_PRJ_TYPE_KEY, INVESTMENT_KEY, IS_FINISH_IMPOSED, IS_PROGRAM, IS_REQ_APPROVAL_REQUIRED, IS_START_IMPOSED, OBJECTIVE, OBJ_STAKEHOLDER2_CAPTION, PROJECT_MGMT_OFFICE_KEY, RISK_FLEXIBILITY_KEY, RISK_FUNDING_KEY, RISK_HUMAN_INTERFACE_KEY, RISK_IMPLEMENTATION_KEY, RISK_INTERDEPENDENCY_KEY, RISK_OBJECTIVES_KEY, RISK_ORG_CULTURE_KEY, RISK_RESOURCE_AVAIL_KEY, RISK_SPONSORSHIP_KEY, RISK_SUPPORTABILITY_KEY, RISK_TECHNICAL_KEY, SPONSORED_BY, STATUS_REPORTING_KEY, Z_ACHIVED_DELIVERAB, Z_ANNUAL_SALES_VOL, Z_ANNUAL_SALES_VOLY1, Z_ANNUAL_SALES_VOLY2, Z_ANNUAL_SALES_VOLY3, Z_ANN_TOV_CALC, Z_ANN_VOL, Z_AUT_ITEM_NUMBER, Z_BALANCE_ATTACK_KEY, Z_BOOKED_BUSINESS_KEY, Z_COMMENT, Z_COMPOUND_KEY, Z_CONGLOMERATE, Z_CUSTOMER_NAME_IN, Z_CUST_CLASSIFICAT, Z_CUST_NAME_NEW, Z_CUST_PART_NO, Z_ENG_ACTION_REQUEST, Z_FIRST_SUPPLY_DATE, Z_GOODSCOST, Z_MULTIPART, Z_PARTS_APPLICATION_KEY, Z_PRJ_ESTCOMPLDATE, Z_PROJECT_TYPE_KEY, Z_PROPOSAL_DATE_INDI, Z_TF_ACTIVITIES_NEXT, Z_TF_ASSUMP_CONS_RI, Z_TF_BDG_CMT, Z_TF_BDG_PL, Z_TF_BDG_PL_COM, Z_TF_BUSINESS_MGR_CAPTION, Z_TF_BUSINESS_MGR_KEY, Z_TF_CUST_DRWG_NO, Z_TF_EXE_SUMM, Z_TF_FEB, Z_TF_FLEX_COMMENTS, Z_TF_HIGH_LOW, Z_TF_IMPL_COMMENTS, Z_TF_JAN, Z_TF_KEY_LEARN, Z_TF_OBJ_COMMENTS, Z_TF_PRJ_BOUNDARIS, Z_TF_PRJ_DELIV, Z_TF_PRJ_HEALTH_STAT_KEY, Z_TF_PRJ_LVL_KEY, Z_TF_PRJ_OBJECTIVE, Z_TF_PRJ_PHASE_KEY, Z_TF_PRJ_STATUS_KEY, Z_TF_PROD_GRP_KEY, Z_TF_PROJ_SPONS_CAPTION, Z_TF_PROJ_SPONS_KEY, Z_TF_STAKEHOLDER_AD, Z_TF_TIM_PL, Z_TF_TSS_DRWG_NO, Z_TF_TSS_PART_NO, Z_TSS_QUOTATION_NO, to_date(''2019/06/18 10:39:39'', ''yyyy/mm/dd HH24:MI:SS'') as dw_updated_date from DWH_PROJECT_V@CLARITYDBLINK A where LAST_UPDATED_DATE >= to_date(''1910/01/01 00:00:00'', ''yyyy/MM/dd HH24:mi:ss'') AND LAST_UPDATED_DATE <= (SELECT DISTINCT DWH_DIM_START_DATE FROM DWH_CFG_SETTINGS) AND A.LANGUAGE_CODE =''en''';
OPEN l_cursor FOR V_SQL_TEXT;
LOOP
FETCH l_cursor BULK COLLECT INTO l_data LIMIT 50000;
FORALL i IN 1..l_data.COUNT
INSERT INTO DWH_INV_PROJECT (AS_OF_DATE, BUSINESS_OWNER, BUSINESS_OWNER_KEY, ESTIMATING_FACTOR_1, ESTIMATING_FACTOR_2, EV_CALC_METHOD_KEY, EV_RPT_PERIOD, EV_RPT_PERIOD_KEY, FINANCE_GOVERNANCE, FINANCE_GOVERNANCE_KEY, IDEA_PRJ_CATEGORY_KEY, IDEA_PRJ_TYPE_KEY, INVESTMENT_KEY, IS_FINISH_IMPOSED, IS_PROGRAM, IS_REQ_APPROVAL_REQUIRED, IS_START_IMPOSED, OBJECTIVE, PROJECT_MGMT_OFFICE, PROJECT_MGMT_OFFICE_KEY, RISK_FLEXIBILITY_KEY, RISK_FUNDING_KEY, RISK_HUMAN_INTERFACE_KEY, RISK_IMPLEMENTATION_KEY, RISK_INTERDEPENDENCY_KEY, RISK_OBJECTIVES_KEY, RISK_ORG_CULTURE_KEY, RISK_RESOURCE_AVAIL_KEY, RISK_SPONSORSHIP_KEY, RISK_SUPPORTABILITY_KEY, RISK_TECHNICAL_KEY, SPONSORED_BY, STATUS_REPORTING_KEY, Z_ACHIVED_DELIVERAB, Z_ANNUAL_SALES_VOL, Z_ANNUAL_SALES_VOLY1, Z_ANNUAL_SALES_VOLY2, Z_ANNUAL_SALES_VOLY3, Z_ANN_TOV_CALC, Z_ANN_VOL, Z_AUT_ITEM_NUMBER, Z_BALANCE_ATTACK_KEY, Z_BOOKED_BUSINESS_KEY, Z_COMMENT, Z_COMPOUND_KEY, Z_CONGLOMERATE, Z_CUSTOMER_NAME_IN, Z_CUST_CLASSIFICAT, Z_CUST_NAME_NEW, Z_CUST_PART_NO, Z_ENG_ACTION_REQUEST, Z_FIRST_SUPPLY_DATE, Z_GOODSCOST, Z_MULTIPART, Z_PARTS_APPLICATION_KEY, Z_PRJ_ESTCOMPLDATE, Z_PROJECT_TYPE_KEY, Z_PROPOSAL_DATE_INDI, Z_TF_ACTIVITIES_NEXT, Z_TF_ASSUMP_CONS_RI, Z_TF_BDG_CMT, Z_TF_BDG_PL, Z_TF_BDG_PL_COM, Z_TF_BUSINESS_MGR, Z_TF_BUSINESS_MGR_KEY, Z_TF_CUST_DRWG_NO, Z_TF_EXE_SUMM, Z_TF_FEB, Z_TF_FLEX_COMMENTS, Z_TF_HIGH_LOW, Z_TF_IMPL_COMMENTS, Z_TF_JAN, Z_TF_KEY_LEARN, Z_TF_OBJ_COMMENTS, Z_TF_PRJ_BOUNDARIS, Z_TF_PRJ_DELIV, Z_TF_PRJ_HEALTH_STAT_KEY, Z_TF_PRJ_LVL_KEY, Z_TF_PRJ_OBJECTIVE, Z_TF_PRJ_PHASE_KEY, Z_TF_PRJ_STATUS_KEY, Z_TF_PROD_GRP_KEY, Z_TF_PROJ_SPONS, Z_TF_PROJ_SPONS_KEY, Z_TF_STAKEHOLDER_AD, Z_TF_TIM_PL, Z_TF_TSS_DRWG_NO, Z_TF_TSS_PART_NO, Z_TSS_QUOTATION_NO, dw_updated_date) VALUES (l_data(i).AS_OF_DATE, l_data(i).OBJ_STAKEHOLDER1_CAPTION, l_data(i).BUSINESS_OWNER_KEY, l_data(i).ESTIMATING_FACTOR_1, l_data(i).ESTIMATING_FACTOR_2, l_data(i).EV_CALC_METHOD_KEY, l_data(i).EV_PERIOD_ID_CAPTION, l_data(i).EV_RPT_PERIOD_KEY, l_data(i).OBJ_STAKEHOLDER3_CAPTION, l_data(i).FINANCE_GOVERNANCE_KEY, l_data(i).IDEA_PRJ_CATEGORY_KEY, l_data(i).IDEA_PRJ_TYPE_KEY, l_data(i).INVESTMENT_KEY, l_data(i).IS_FINISH_IMPOSED, l_data(i).IS_PROGRAM, l_data(i).IS_REQ_APPROVAL_REQUIRED, l_data(i).IS_START_IMPOSED, l_data(i).OBJECTIVE, l_data(i).OBJ_STAKEHOLDER2_CAPTION, l_data(i).PROJECT_MGMT_OFFICE_KEY, l_data(i).RISK_FLEXIBILITY_KEY, l_data(i).RISK_FUNDING_KEY, l_data(i).RISK_HUMAN_INTERFACE_KEY, l_data(i).RISK_IMPLEMENTATION_KEY, l_data(i).RISK_INTERDEPENDENCY_KEY, l_data(i).RISK_OBJECTIVES_KEY, l_data(i).RISK_ORG_CULTURE_KEY, l_data(i).RISK_RESOURCE_AVAIL_KEY, l_data(i).RISK_SPONSORSHIP_KEY, l_data(i).RISK_SUPPORTABILITY_KEY, l_data(i).RISK_TECHNICAL_KEY, l_data(i).SPONSORED_BY, l_data(i).STATUS_REPORTING_KEY, l_data(i).Z_ACHIVED_DELIVERAB, l_data(i).Z_ANNUAL_SALES_VOL, l_data(i).Z_ANNUAL_SALES_VOLY1, l_data(i).Z_ANNUAL_SALES_VOLY2, l_data(i).Z_ANNUAL_SALES_VOLY3, l_data(i).Z_ANN_TOV_CALC, l_data(i).Z_ANN_VOL, l_data(i).Z_AUT_ITEM_NUMBER, l_data(i).Z_BALANCE_ATTACK_KEY, l_data(i).Z_BOOKED_BUSINESS_KEY, l_data(i).Z_COMMENT, l_data(i).Z_COMPOUND_KEY, l_data(i).Z_CONGLOMERATE, l_data(i).Z_CUSTOMER_NAME_IN, l_data(i).Z_CUST_CLASSIFICAT, l_data(i).Z_CUST_NAME_NEW, l_data(i).Z_CUST_PART_NO, l_data(i).Z_ENG_ACTION_REQUEST, l_data(i).Z_FIRST_SUPPLY_DATE, l_data(i).Z_GOODSCOST, l_data(i).Z_MULTIPART, l_data(i).Z_PARTS_APPLICATION_KEY, l_data(i).Z_PRJ_ESTCOMPLDATE, l_data(i).Z_PROJECT_TYPE_KEY, l_data(i).Z_PROPOSAL_DATE_INDI, l_data(i).Z_TF_ACTIVITIES_NEXT, l_data(i).Z_TF_ASSUMP_CONS_RI, l_data(i).Z_TF_BDG_CMT, l_data(i).Z_TF_BDG_PL, l_data(i).Z_TF_BDG_PL_COM, l_data(i).Z_TF_BUSINESS_MGR_CAPTION, l_data(i).Z_TF_BUSINESS_MGR_KEY, l_data(i).Z_TF_CUST_DRWG_NO, l_data(i).Z_TF_EXE_SUMM, l_data(i).Z_TF_FEB, l_data(i).Z_TF_FLEX_COMMENTS, l_data(i).Z_TF_HIGH_LOW, l_data(i).Z_TF_IMPL_COMMENTS, l_data(i).Z_TF_JAN, l_data(i).Z_TF_KEY_LEARN, l_data(i).Z_TF_OBJ_COMMENTS, l_data(i).Z_TF_PRJ_BOUNDARIS, l_data(i).Z_TF_PRJ_DELIV, l_data(i).Z_TF_PRJ_HEALTH_STAT_KEY, l_data(i).Z_TF_PRJ_LVL_KEY, l_data(i).Z_TF_PRJ_OBJECTIVE, l_data(i).Z_TF_PRJ_PHASE_KEY, l_data(i).Z_TF_PRJ_STATUS_KEY, l_data(i).Z_TF_PROD_GRP_KEY, l_data(i).Z_TF_PROJ_SPONS_CAPTION, l_data(i).Z_TF_PROJ_SPONS_KEY, l_data(i).Z_TF_STAKEHOLDER_AD, l_data(i).Z_TF_TIM_PL, l_data(i).Z_TF_TSS_DRWG_NO, l_data(i).Z_TF_TSS_PART_NO, l_data(i).Z_TSS_QUOTATION_NO, l_data(i).dw_updated_date);
COMMIT;
EXIT WHEN l_cursor%NOTFOUND;
END LOOP;
CLOSE l_cursor;
CMN_SAVEDROP_CREATE_INDEXES_SP('DWH_INV_PROJECT','CREATE');
EXCEPTION
WHEN OTHERS THEN
BEGIN
V_SQLCODE := SQLCODE;
V_SQLERRM := SQLERRM;
V_SQL_TEXT := 'INSERT INTO DWH_INV_PROJECT (AS_OF_DATE, BUSINESS_OWNER, BUSINESS_OWNER_KEY, ESTIMATING_FACTOR_1, ESTIMATING_FACTOR_2, EV_CALC_METHOD_KEY, EV_RPT_PERIOD, EV_RPT_PERIOD_KEY, FINANCE_GOVERNANCE, FINANCE_GOVERNANCE_KEY, IDEA_PRJ_CATEGORY_KEY, IDEA_PRJ_TYPE_KEY, INVESTMENT_KEY, IS_FINISH_IMPOSED, IS_PROGRAM, IS_REQ_APPROVAL_REQUIRED, IS_START_IMPOSED, OBJECTIVE, PROJECT_MGMT_OFFICE, PROJECT_MGMT_OFFICE_KEY, RISK_FLEXIBILITY_KEY, RISK_FUNDING_KEY, RISK_HUMAN_INTERFACE_KEY, RISK_IMPLEMENTATION_KEY, RISK_INTERDEPENDENCY_KEY, RISK_OBJECTIVES_KEY, RISK_ORG_CULTURE_KEY, RISK_RESOURCE_AVAIL_KEY, RISK_SPONSORSHIP_KEY, RISK_SUPPORTABILITY_KEY, RISK_TECHNICAL_KEY, SPONSORED_BY, STATUS_REPORTING_KEY, Z_ACHIVED_DELIVERAB, Z_ANNUAL_SALES_VOL, Z_ANNUAL_SALES_VOLY1, Z_ANNUAL_SALES_VOLY2, Z_ANNUAL_SALES_VOLY3, Z_ANN_TOV_CALC, Z_ANN_VOL, Z_AUT_ITEM_NUMBER, Z_BALANCE_ATTACK_KEY, Z_BOOKED_BUSINESS_KEY, Z_COMMENT, Z_COMPOUND_KEY, Z_CONGLOMERATE, Z_CUSTOMER_NAME_IN, Z_CUST_CLASSIFICAT, Z_CUST_NAME_NEW, Z_CUST_PART_NO, Z_ENG_ACTION_REQUEST, Z_FIRST_SUPPLY_DATE, Z_GOODSCOST, Z_MULTIPART, Z_PARTS_APPLICATION_KEY, Z_PRJ_ESTCOMPLDATE, Z_PROJECT_TYPE_KEY, Z_PROPOSAL_DATE_INDI, Z_TF_ACTIVITIES_NEXT, Z_TF_ASSUMP_CONS_RI, Z_TF_BDG_CMT, Z_TF_BDG_PL, Z_TF_BDG_PL_COM, Z_TF_BUSINESS_MGR, Z_TF_BUSINESS_MGR_KEY, Z_TF_CUST_DRWG_NO, Z_TF_EXE_SUMM, Z_TF_FEB, Z_TF_FLEX_COMMENTS, Z_TF_HIGH_LOW, Z_TF_IMPL_COMMENTS, Z_TF_JAN, Z_TF_KEY_LEARN, Z_TF_OBJ_COMMENTS, Z_TF_PRJ_BOUNDARIS, Z_TF_PRJ_DELIV, Z_TF_PRJ_HEALTH_STAT_KEY, Z_TF_PRJ_LVL_KEY, Z_TF_PRJ_OBJECTIVE, Z_TF_PRJ_PHASE_KEY, Z_TF_PRJ_STATUS_KEY, Z_TF_PROD_GRP_KEY, Z_TF_PROJ_SPONS, Z_TF_PROJ_SPONS_KEY, Z_TF_STAKEHOLDER_AD, Z_TF_TIM_PL, Z_TF_TSS_DRWG_NO, Z_TF_TSS_PART_NO, Z_TSS_QUOTATION_NO, dw_updated_date) VALUES (l_data(i).AS_OF_DATE, l_data(i).OBJ_STAKEHOLDER1_CAPTION, l_data(i).BUSINESS_OWNER_KEY, l_data(i).ESTIMATING_FACTOR_1, l_data(i).ESTIMATING_FACTOR_2, l_data(i).EV_CALC_METHOD_KEY, l_data(i).EV_PERIOD_ID_CAPTION, l_data(i).EV_RPT_PERIOD_KEY, l_data(i).OBJ_STAKEHOLDER3_CAPTION, l_data(i).FINANCE_GOVERNANCE_KEY, l_data(i).IDEA_PRJ_CATEGORY_KEY, l_data(i).IDEA_PRJ_TYPE_KEY, l_data(i).INVESTMENT_KEY, l_data(i).IS_FINISH_IMPOSED, l_data(i).IS_PROGRAM, l_data(i).IS_REQ_APPROVAL_REQUIRED, l_data(i).IS_START_IMPOSED, l_data(i).OBJECTIVE, l_data(i).OBJ_STAKEHOLDER2_CAPTION, l_data(i).PROJECT_MGMT_OFFICE_KEY, l_data(i).RISK_FLEXIBILITY_KEY, l_data(i).RISK_FUNDING_KEY, l_data(i).RISK_HUMAN_INTERFACE_KEY, l_data(i).RISK_IMPLEMENTATION_KEY, l_data(i).RISK_INTERDEPENDENCY_KEY, l_data(i).RISK_OBJECTIVES_KEY, l_data(i).RISK_ORG_CULTURE_KEY, l_data(i).RISK_RESOURCE_AVAIL_KEY, l_data(i).RISK_SPONSORSHIP_KEY, l_data(i).RISK_SUPPORTABILITY_KEY, l_data(i).RISK_TECHNICAL_KEY, l_data(i).SPONSORED_BY, l_data(i).STATUS_REPORTING_KEY, l_data(i).Z_ACHIVED_DELIVERAB, l_data(i).Z_ANNUAL_SALES_VOL, l_data(i).Z_ANNUAL_SALES_VOLY1, l_data(i).Z_ANNUAL_SALES_VOLY2, l_data(i).Z_ANNUAL_SALES_VOLY3, l_data(i).Z_ANN_TOV_CALC, l_data(i).Z_ANN_VOL, l_data(i).Z_AUT_ITEM_NUMBER, l_data(i).Z_BALANCE_ATTACK_KEY, l_data(i).Z_BOOKED_BUSINESS_KEY, l_data(i).Z_COMMENT, l_data(i).Z_COMPOUND_KEY, l_data(i).Z_CONGLOMERATE, l_data(i).Z_CUSTOMER_NAME_IN, l_data(i).Z_CUST_CLASSIFICAT, l_data(i).Z_CUST_NAME_NEW, l_data(i).Z_CUST_PART_NO, l_data(i).Z_ENG_ACTION_REQUEST, l_data(i).Z_FIRST_SUPPLY_DATE, l_data(i).Z_GOODSCOST, l_data(i).Z_MULTIPART, l_data(i).Z_PARTS_APPLICATION_KEY, l_data(i).Z_PRJ_ESTCOMPLDATE, l_data(i).Z_PROJECT_TYPE_KEY, l_data(i).Z_PROPOSAL_DATE_INDI, l_data(i).Z_TF_ACTIVITIES_NEXT, l_data(i).Z_TF_ASSUMP_CONS_RI, l_data(i).Z_TF_BDG_CMT, l_data(i).Z_TF_BDG_PL, l_data(i).Z_TF_BDG_PL_COM, l_data(i).Z_TF_BUSINESS_MGR_CAPTION, l_data(i).Z_TF_BUSINESS_MGR_KEY, l_data(i).Z_TF_CUST_DRWG_NO, l_data(i).Z_TF_EXE_SUMM, l_data(i).Z_TF_FEB, l_data(i).Z_TF_FLEX_COMMENTS, l_data(i).Z_TF_HIGH_LOW, l_data(i).Z_TF_IMPL_COMMENTS, l_data(i).Z_TF_JAN, l_data(i).Z_TF_KEY_LEARN, l_data(i).Z_TF_OBJ_COMMENTS, l_data(i).Z_TF_PRJ_BOUNDARIS, l_data(i).Z_TF_PRJ_DELIV, l_data(i).Z_TF_PRJ_HEALTH_STAT_KEY, l_data(i).Z_TF_PRJ_LVL_KEY, l_data(i).Z_TF_PRJ_OBJECTIVE, l_data(i).Z_TF_PRJ_PHASE_KEY, l_data(i).Z_TF_PRJ_STATUS_KEY, l_data(i).Z_TF_PROD_GRP_KEY, l_data(i).Z_TF_PROJ_SPONS_CAPTION, l_data(i).Z_TF_PROJ_SPONS_KEY, l_data(i).Z_TF_STAKEHOLDER_AD, l_data(i).Z_TF_TIM_PL, l_data(i).Z_TF_TSS_DRWG_NO, l_data(i).Z_TF_TSS_PART_NO, l_data(i).Z_TSS_QUOTATION_NO, l_data(i).dw_updated_date)';
ROLLBACK;
INSERT INTO DWH_CMN_ERROR_MESSAGE VALUES('DWH_INV_PROJECT',V_SQLERRM,V_SQLCODE,REPLACE(V_SQL_TEXT,'**',''''));
COMMIT;
RAISE_APPLICATION_ERROR(-20100,'ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_INV_PROJECT). SQLERRM : ' || V_SQLERRM);
END;
END;
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_INV_PROJECT). SQLERRM : ORA-00904: "Z_TF_TIM_PL": invalid identifier
ORA-06512: at line 31
INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [Full Insert-orcl] (result=[false])
INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [clarity - isOracle? 2] (result=[false])
INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [Delete all records] (result=[false])
INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [DWH_DIM_FULL_LD_MAPPING] (result=[false])
INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [is Full Refresh?] (result=[false])
INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [no Records got Updated?] (result=[false])
INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [DWH_DIM_HAS_RECORDS] (result=[false])
INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [Use DataPump?] (result=[false])
INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [Full Load(O)?] (result=[false])
INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [set variables - oracle] (result=[false])
INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [clarity - isOracle?] (result=[false])
INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [DWH_DIM_FIELD_MAPPING] (result=[false])
INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [Pre execution] (result=[false])
INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Job execution finished
ERROR 2019-06-18 10:39:41,212 [dwh_etl_interface_dim - User Defined Java Class] dwh.event User Defined Java Class - Unexpected error
ERROR 2019-06-18 10:39:41,230 [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_PROJECT
Original Message:
Sent: 06-11-2019 05:47 AM
From: Divya J
Subject: Create Jaspersoft Job failing
Thanks Suman. That worked.
Original Message:
Sent: 06-10-2019 03:30 AM
From: Suman Pramanik
Subject: Create Jaspersoft Job failing
Hi Divya
As you refresh you would need to do the following steps
- Generate the keystore and copy to Jaspersoft server
- Login to Jaspersoft console and Search for user jasperadmin under the tenant which is linked to clarity instance
- Edit the user and look for tab Attributes on the right-hand panel
- Delete all the attributes except domain Whitelist as that is read only
- Now login to Clarity application server
- Navigate to CLARITY_HOME\bin and execute update jasperParameters
- Run the Job Now.
Hope this helps
------------------------------
Thanks & Regards
Suman Pramanik
Principal Support Engineer | Customer Success & Support, Enterprise Software Division
Broadcom
Original Message:
Sent: 06-10-2019 03:11 AM
From: Divya J
Subject: Create Jaspersoft Job failing
HI,
Recently we did a Refresh of our DEV system with PROD Data.
And we see that Create Update Jaspersoft Job is failing with message
Failed to get an instance of ClarityJasperAdmin :: Exception getting ClarityJasperAdmin : Unauthorized access to http://<url>/reportservice with username ppmjasperadmin.
i tried this command "admin jaspersoft syncPPMContext -userName superuser -password superuser",
but No luck. Do I need to regenerate Keystore file? Please help me to know what is wrong here and how to solve this.
Thanks,
Divya