XMLELEMENT Query Fails With ORA-7445 [qmxtkAggCrtAggContext()+240] On 11.2.0.4

Last updated on JANUARY 24, 2018

Applies to:

Symptoms

On 11.2.0.4, an XMLELEMENT query fails with the following error:

ORA-07445 [qmxtkAggCrtAggContext()+240] [SIGSEGV] [ADDR:0x200000018] [PC:0x108599AF0] [Address not mapped to object]


Statement that fails:

SELECT
  /*+ RULE */
  XMLELEMENT( "MessageToAutomatedSystem", XMLATTRIBUTES('http://download.pro6tem-sas.com/OriAutomate/Schema' AS "xmlns:ns3", 'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi", 'MessageToAutomatedSystem_v3.xsd' AS "xsi:noNamespaceSchemaLocation"), XMLELEMENT( "ns3:Issuer", XMLELEMENT( "ns3:Registration", XMLELEMENT( "ns3:Identifier", 'VALACTA'), XMLELEMENT( "ns3:IdentifierType", 'BodyIdentifier') ) ), XMLELEMENT( "ns3:Owner", XMLELEMENT( "ns3:Registration", XMLELEMENT( "ns3:Identifier", 'CAN' || LPAD(HRD.PRV_CD, 2, '0') || '0' || LPAD(HRD.ID, 5, '0')), XMLELEMENT( "ns3:IdentifierType", 'InternationalFarmNumberCodification') ) ), XMLELEMENT( "ns3:Name", 'MessageToAutomatedSystem'), XMLELEMENT( "ns3:Creation", TO_CHAR(:B3 , 'YYYY-MM-DD') || 'T' || TO_CHAR(:B3 , 'HH24:MI:SS')), XMLELEMENT( "ns3:Farm", XMLELEMENT( "ns3:Immatriculation", XMLELEMENT( "ns3:Identifier", 'CAN' || LPAD(HRD.PRV_CD, 2, '0') || '0' || LPAD(HRD.ID, 5, '0')), XMLELEMENT( "ns3:IdentifierType", 'InternationalFarmNumberCodification') ),
  (
  SELECT
  XMLELEMENT( "ns3:FarmName", HCT.FARM_NAME)
  FROM
  CMI_HERD_CONTACTS HCT
  WHERE
  HCT.HRD_ID = HRD.ID
  AND HCT.HRD_PRV_CD = HRD.PRV_CD
  AND HCT.RSPBLTY_IND = '1'
  AND HCT.OWNER_TYPE = '1'
  AND HCT.END_DATE IS NULL
  )
), XMLELEMENT( "ExportParameters",
  (
  SELECT
  XMLELEMENT( "ns3:BeginDate", TO_CHAR(MAX(HTP.TEST_DATE), 'YYYY-MM-DD'))
  FROM
  CMI_HERD_TEST_PERIODS HTP
  WHERE
  HTP.HRD_ID = HRD.ID
  AND HTP.HRD_PRV_CD = HRD.PRV_CD
  AND HTP.PROCSD_DATE IS NOT NULL
  )
, XMLELEMENT( "ns3:EndDate", TO_CHAR(:B3 , 'YYYY-MM-DD')) ), XMLFOREST( (
  SELECT
  XMLAGG( XMLELEMENT( "Animal", XMLELEMENT( "AnimalIdentity", XMLELEMENT( "AnimalId" , XMLELEMENT( "ns3:RegistrationAnimalNumber", NVL(ANM.PERM_ID, ANM.REG_ID)), XMLELEMENT( "ns3:AutomatedAnimalNumber", TRIM(HAN.VISIBLE_ID_NO_6)) ) , XMLFOREST(TO_CHAR(ANM.BIRTH_DATE, 'YYYY-MM-DD') AS "ns3:BirthDate", ANM.ANB_CD AS "ns3:Breed", DECODE(ANM.SEX, 'F', 2, 1) AS "ns3:Gender", TRIM(HAN.VISIBLE_ID_NO_6) AS "ns3:WorkNumber", HAN.BARN_NAME AS "ns3:AnimalName", 'Milk' AS "ns3:ProductType", DECODE(ANM.SPECIES, 1, 'B', 2, 'G', 3, 'O') AS "ns3:Species") ), XMLELEMENT( "ns3:ActionCode", 'INF'), XMLELEMENT( "ns3:HerdMovement", XMLELEMENT( "ns3:EnterHerdDate", TO_CHAR(HAN.ENTER_HERD_DATE, 'YYYY-MM-DD')), XMLFOREST(TO_CHAR(HAN.LEFT_HERD_DATE, 'YYYY-MM-DD') AS "ns3:ExitHerdDate") ), XMLFOREST( XMLCONCAT( (
  SELECT
  XMLAGG( XMLELEMENT( "AnimalEvent", XMLFOREST(TO_CHAR(BRD.SERVICE_DATE, 'YYYY-MM-DD') AS "ns3:BeginDate", 'INF' AS "ns3:ActionCode"), XMLELEMENT( "AnimalEventType", XMLELEMENT( "ns3:Insemination",
  (
  SELECT
  XMLELEMENT( "ns3:Bull", XMLELEMENT( "ns3:RegistrationAnimalNumber", RPAD(BRD2.COU_CD_SIRE, 3, ' ') || BRD2.SIRE_REG_ID) )
  FROM
  CMI_BREEDINGS BRD2
  WHERE
  BRD2.ANM_ID = BRD.ANM_ID
  AND BRD2.SERVICE_DATE = BRD.SERVICE_DATE
  AND BRD2.CERTIFICATE_NUM = BRD.CERTIFICATE_NUM
  AND BRD2.SIRE_REG_ID IS NOT NULL
  )
) ) )
  ORDER BY
  BRD.SERVICE_DATE )
  FROM
  CMI_BREEDINGS BRD
  WHERE
  BRD.ANM_ID = ANM.ID ),
  (
  SELECT
  XMLAGG( XMLELEMENT( "AnimalEvent", XMLFOREST(TO_CHAR(LCT.START_DATE, 'YYYY-MM-DD') AS "ns3:BeginDate", 'INF' AS "ns3:ActionCode"), XMLELEMENT( "AnimalEventType", XMLELEMENT( "ns3:Parturition", XMLELEMENT( "ns3:LactationNumber", LCT.LACT_NO) ) ) )
  ORDER BY
  LCT.START_DATE )
  FROM
  CMI_LACTATIONS LCT
  WHERE
  LCT.ANM_ID = ANM.ID
  )
,
  (
  SELECT
  XMLAGG( XMLELEMENT( "AnimalEvent", XMLFOREST(TO_CHAR(LCT.END_DATE, 'YYYY-MM-DD') AS "ns3:BeginDate", 'INF' AS "ns3:ActionCode"), XMLELEMENT( "AnimalEventType", XMLELEMENT( "ns3:DryOff") ) )
  ORDER BY
  LCT.END_DATE )
  FROM
  CMI_LACTATIONS LCT
  WHERE
  LCT.ANM_ID = ANM.ID
  AND LCT.END_DATE IS NOT NULL
  )
,
  (
  SELECT
  XMLAGG( XMLELEMENT( "AnimalEvent", XMLFOREST(TO_CHAR(PRG.PRGNCY_CHK_DATE, 'YYYY-MM-DD') AS "ns3:BeginDate", 'INF' AS "ns3:ActionCode"), XMLELEMENT( "AnimalEventType", XMLELEMENT( "ns3:PregnancyCheck", XMLELEMENT( "ns3:PregnancyCheckResult", DECODE(PRG.PRGNCY_IND, '1', 'F', 'E')) ) ) )
  ORDER BY
  PRG.PRGNCY_CHK_DATE )
  FROM
  CMI_PREGNANCIES PRG
  WHERE
  PRG.ANM_ID = ANM.ID
  )
) AS "AnimalEvents" ),
  (
  SELECT
  XMLFOREST( XMLCONCAT( XMLAGG( XMLELEMENT( "ns3:OfficialTest", XMLELEMENT( "ns3:OfficialTestDate", TO_CHAR(HTP1.TEST_DATE, 'YYYY-MM-DD')), XMLFOREST( XMLCONCAT( (
  SELECT
  XMLELEMENT( "ns3:MilkComponentAnalysis", XMLELEMENT( "ns3:AnalysedComponent", 'FatPercentage'), XMLELEMENT( "ns3:AnalysedComponentValue", DECODE(ATY1.HR_24_YLD, 0, '0', LTRIM(TO_CHAR((ATY2.HR_24_YLD / ATY1.HR_24_YLD * 100) * 100, '9990')))), XMLELEMENT( "ns3:ValueUnit", '1/10000'), XMLELEMENT( "ns3:ActionCode", 'INF') )
  FROM
  CMI_ANIMAL_TEST_YIELDS ATY2
  WHERE
  ATY2.ATS_LCS_ANM_ID = ATS.LCS_ANM_ID
  AND ATY2.ATS_LCS_LACT_NO = ATS.LCS_LACT_NO
  AND ATY2.ATS_HTP_ID = ATS.HTP_ID
  AND ATY2.YLD_TYPE = '02'
  AND ATY2.HR_24_YLD IS NOT NULL ),
  (
  SELECT
  XMLELEMENT( "ns3:MilkComponentAnalysis", XMLELEMENT( "ns3:AnalysedComponent", 'ProteinPercentage'), XMLELEMENT( "ns3:AnalysedComponentValue", DECODE(ATY1.HR_24_YLD, 0, '0', LTRIM(TO_CHAR((ATY2.HR_24_YLD / ATY1.HR_24_YLD * 100) * 100, '9990')))), XMLELEMENT( "ns3:ValueUnit", '1/10000'), XMLELEMENT( "ns3:ActionCode", 'INF') )
  FROM
  CMI_ANIMAL_TEST_YIELDS ATY2
  WHERE
  ATY2.ATS_LCS_ANM_ID = ATS.LCS_ANM_ID
  AND ATY2.ATS_LCS_LACT_NO = ATS.LCS_LACT_NO
  AND ATY2.ATS_HTP_ID = ATS.HTP_ID
  AND ATY2.YLD_TYPE = '03'
  AND ATY2.HR_24_YLD IS NOT NULL
  )
,
  (
  SELECT
  XMLELEMENT( "ns3:MilkComponentAnalysis", XMLELEMENT( "ns3:AnalysedComponent", 'SomaticCellCount'), XMLELEMENT( "ns3:AnalysedComponentValue", LTRIM(TO_CHAR(LCP.MSRMNT))), XMLELEMENT( "ns3:ValueUnit", 'mil/ml'), XMLELEMENT( "ns3:ActionCode", 'INF') )
  FROM
  CMI_LAB_COMPONENTS LCP
  WHERE
  LCP.ASM_ANM_ID = ATS.LCS_ANM_ID
  AND LCP.ASM_HTP_ID = ATS.HTP_ID
  AND LCP.COMPNT_TYPE = '04'
  AND LCP.MSRMNT IS NOT NULL
  )
,
  (
  SELECT
  XMLELEMENT( "ns3:MilkComponentAnalysis", XMLELEMENT( "ns3:AnalysedComponent", 'Urea'), XMLELEMENT( "ns3:AnalysedComponentValue", LTRIM(TO_CHAR(LCP.MSRMNT * 10))), XMLELEMENT( "ns3:ValueUnit", '1/10000'), XMLELEMENT( "ns3:ActionCode", 'INF') )
  FROM
  CMI_LAB_COMPONENTS LCP
  WHERE
  LCP.ASM_ANM_ID = ATS.LCS_ANM_ID
  AND LCP.ASM_HTP_ID = ATS.HTP_ID
  AND LCP.COMPNT_TYPE = '05'
  AND LCP.MSRMNT IS NOT NULL
  )
,
  (
  SELECT
  XMLELEMENT( "ns3:MilkComponentAnalysis", XMLELEMENT( "ns3:AnalysedComponent", 'Lactose'), XMLELEMENT( "ns3:AnalysedComponentValue", LTRIM(TO_CHAR(LCP.MSRMNT * 100))), XMLELEMENT( "ns3:ValueUnit", '1/10000'), XMLELEMENT( "ns3:ActionCode", 'INF') )
  FROM
  CMI_LAB_COMPONENTS LCP
  WHERE
  LCP.ASM_ANM_ID = ATS.LCS_ANM_ID
  AND LCP.ASM_HTP_ID = ATS.HTP_ID
  AND LCP.COMPNT_TYPE = '07'
  AND LCP.MSRMNT IS NOT NULL
  )
) AS "ns3:MilkAnalysis" ), XMLELEMENT( "ns3:TestDayMilkWeight", LTRIM(TO_CHAR(LEAST(ATY1.HR_24_YLD, 99.9) * 10, '990'))) ) ) ) AS "ns3:OfficialTests" )
  FROM
  CMI_HERD_TEST_PERIODS HTP1,
  CMI_ANIMAL_TESTS ATS,
  CMI_ANIMAL_TEST_YIELDS ATY1
  WHERE
  HTP1.HRD_ID = HAN.HRD_ID
  AND HTP1.HRD_PRV_CD = HAN.HRD_PRV_CD
  AND HTP1.TEST_DATE >= ADD_MONTHS(SYSDATE, - 6)
  AND ATS.LCS_ANM_ID = ANM.ID
  AND ATS.HTP_ID = HTP1.ID
  AND ATY1.ATS_LCS_ANM_ID = ATS.LCS_ANM_ID
  AND ATY1.ATS_LCS_LACT_NO = ATS.LCS_LACT_NO
  AND ATY1.ATS_HTP_ID = ATS.HTP_ID
  AND ATY1.YLD_TYPE = '01'
  AND ATY1.HR_24_YLD IS NOT NULL
  )
)
  ORDER BY
  HAN.VISIBLE_ID_NO_6 )
  FROM
  CMI_HERD_ANIMALS HAN, CMI_ANIMALS ANM
  WHERE
  HAN.HRD_ID = HRD.ID
  AND HAN.HRD_PRV_CD = HRD.PRV_CD
  AND HAN.LEFT_HERD_DATE IS NULL
  AND HAN.VISIBLE_ID_NO_6 IS NOT NULL
  AND CCC69000_IS_NUMBER(HAN.VISIBLE_ID_NO_6) = 1
  AND ANM.ID = HAN.ANM_ID
  AND
  (
  ANM.PERM_ID IS NOT NULL
  OR ANM.REG_ID IS NOT NULL
  )
) AS "Animals" ) ).GETCLOBVAL()
  FROM
  CMI_HERDS HRD
  WHERE
  HRD.ID = :B2
  AND HRD.PRV_CD = :B1


If the ORDER BY is removed from the query it executes successfully. Also, this same query does not fail on 9.2.0.8.

Cause