BD - 8125 - Data Truncation Possibility In INSTN_MASTER.ADDR_ST_TX (Doc ID 2989402.1)

Last updated on DECEMBER 01, 2023

Applies to:

Symptoms

BDF FinancialInstitution_FOTPSPopulation datamap query concatenates all address lines 1/2/3/4/5/6 initially as per below code snippet.

  COALESCE((RTRIM(FOTPS.PARTY_ADDR_STRT_LINE1_TX) ||
  RTRIM(' ' || FOTPS.PARTY_ADDR_STRT_LINE2_TX) ||
  RTRIM(' ' || FOTPS.PARTY_ADDR_STRT_LINE3_TX) ||
  RTRIM(' ' || FOTPS.PARTY_ADDR_STRT_LINE4_TX) ||
  RTRIM(' ' || FOTPS.PARTY_ADDR_STRT_LINE5_TX) ||
  RTRIM(' ' || FOTPS.PARTY_ADDR_STRT_LINE6_TX)),
  (RTRIM(' ' || FOTPS.PARTY_ADDR_CITY_NM) ||
  RTRIM(' ' || FOTPS.PARTY_ADDR_STATE_CD) ||
  RTRIM(' ' || FOTPS.PARTY_ADDR_CNTRY_CD) ||
  RTRIM(' ' || FOTPS.PARTY_ADDR_POSTL_CD))) AS V_ADDR_ST

At the end of the query, datamap is restricting the address text to 255 characters, please refer below code snippet
SELECT
TF.PARTY_ID AS INSTN_ID,
TF.PARTY_ID_TYPE_CD AS INSTN_ID_TYPE_CD,
SUBSTR(COALESCE(TF.PARTY_AUG_NM,INSTN_NM),1,105) AS INSTN_NM,
COALESCE(TF.PARTY_ADDR_CNTRY_CD,INSTN_CNTRY_CD) AS INSTN_CNTRY_CD,
SUBSTR(COALESCE(TF.V_ADDR_ST,ADDR_ST_TX),1,255) AS ADDR_ST_TX,
COALESCE(TF.PARTY_ADDR_CITY_NM,ADDR_CITY_NM) AS ADDR_CITY_NM,
  COALESCE(TF.PARTY_ADDR_STATE_CD,ADDR_STATE_CD) AS ADDR_STATE_CD,
  COALESCE(TF.PARTY_ADDR_CNTRY_CD,ADDR_CNTRY_CD) AS ADDR_CNTRY_CD,
  COALESCE(TF.PARTY_ADDR_POSTL_CD,ADDR_POSTL_CD) AS ADDR_POSTL_CD
FROM M0436FOTPSTOIM TF
WHERE DUP=1
AND TF.PARTY_ID IS NOT NULL

Cause

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