ETL Is Failing For B1_BR_SW_PLAN_JOBS (Doc ID 2864315.1)

Last updated on JUNE 04, 2024

Applies to:

Goal

On : 2.7.0.1.3 version, Install / Upgrade

The ETL fails for the B1_BR_SW_PLAN_JOBS table as ETL is trying to merge duplicate records.

ERROR
-----------------------
ODI-1228: Task Procedure-B1_BR_SW_PLAN_JOBS_EXCL-Set Exclude_IND=1 when
switch sheet outage event gets deleted from source fails on the target
connection B1.
Caused By: java.sql.SQLException: ORA-30926: unable to get a stable set of
rows in the source tables
+++++++++

The following merge query fails as we get multiple records for combination of SRC_SW_PLAN_CLS and SRC_SW_PLAN_IDX---

MERGE INTO DWADM.b1_br_sw_plan_jobs TGT
USING (Select TMP.SRC_SW_PLAN_CLS, TMP.SRC_SW_PLAN_IDX, TMP.JOB_NBR,
TMP.UPDATE_DTTM, TMP.IND_UPDATE, S.JRN_FLAG
  from DWSTAGE.KEY_3135450 TMP, NMS2REP.SWITCH_SHEET_OUTAGE_EVENTS S
  WHERE S.SWITCH_SHEET_CLS = TMP.SRC_SW_PLAN_CLS AND S.SWITCH_SHEET_IDX
= TMP.SRC_SW_PLAN_IDX AND TMP.IND_UPDATE= 'D' AND S.JRN_FLAG = 'D') STG
ON (TGT.SRC_SW_PLAN_CLS = STG.SRC_SW_PLAN_CLS AND TGT.SRC_SW_PLAN_IDX =
STG.SRC_SW_PLAN_IDX)
WHEN MATCHED
THEN UPDATE SET
  TGT.EXCLUDE_IND= 1
  , TGT.UPDATE_DTTM = STG.UPDATE_DTTM
  , TGT.JOB_NBR = STG.JOB_NBR
  , TGT.DATA_LOAD_DTTM = SYSDATE
;
++++
 

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.