XMLELEMENT Query Fails With ORA-7445 [qmxtkAggCrtAggContext()+240] On 11.2.0.4
(Doc ID 2352472.1)
Last updated on NOVEMBER 29, 2019
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.4 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Cause |
Solution |