This discussion is archived
2 Replies Latest reply: Mar 12, 2013 7:00 AM by odie_63 RSS

Help on SXI Getting this errors ORA-30986, ORA-29896, ORA-30959

beta32c Newbie
Currently Being Moderated
Hi,

I am stuck with two problems regarding Stuructred XML Index (SXI), please read the below desciptions to find more on the problems
Problems are:-
a) I can't create two virtual columns at the same level (ORA-30986: XML Index with more than one virtual column is disallowed), although i am reading two xml sequences from the same level (EstChargeCatTotal and EstimateLine) , how can i have index on both the XML fragments?
b) Also Oracle doesnot allow the parameter to be more than 1000 characters, i tried using DBMS_XMLINDEX.registerParameter but then the create index (just below) fails with "ORA-30959: The indexed column is not stored in CLOB". Even if i can register it i will be again stuck with Problem a) that is virtual columns issue.
Create index query in this case--> CREATE INDEX ESTLINE_XMLINDEX ON PWAYWORKFILE_TABLE (WORKFILE) INDEXTYPE IS XDB.XMLINDEX;

-> Where PWAYWORKFILE_TABLE is a table with it's WORKFILE column defined as a XMLType.

Heres the XML structure (sample)
==================================
<PwayWorkfile>
<EstimateComp>
<LastOutboxEstID>ID1234</LastOutboxEstID>
<EstList>
<VehEstimate NUMBERLINES="1">
     <EstimateID>01</EstimateID>
     <EstTotals>
     <ChargeCatTotalCollection>
     <EstChargeCatTotal>
     <ChargeSubType>SUb1</ChargeSubType>
     <CHARGECATTOTALAMOUNT>SUB1_1</CHARGECATTOTALAMOUNT>
     </EstChargeCatTotal>
     <EstChargeCatTotal>
     <ChargeSubType>SUb2</ChargeSubType>
     <CHARGECATTOTALAMOUNT>SUB1_2</CHARGECATTOTALAMOUNT>
     </EstChargeCatTotal>
     </ChargeCatTotalCollection>
     </EstTotals>
     <EstimateLines>
     <EstimateLine LINENUMBER="1">
     <LineBlockID>LN_1</LineBlockID>
     <LaborHours>12.09</LaborHours>
     </EstimateLine>
     <EstimateLine LINENUMBER="2">
     <LineBlockID>LN_2</LineBlockID>
     <LaborHours>3.56</LaborHours>
     </EstimateLine>
     </EstimateLines>
</VehEstimate>
<VehEstimate NUMBERLINES="2">
     <EstimateID>02</EstimateID>
     <EstTotals>
     <ChargeCatTotalCollection>
          <EstChargeCatTotal>
               <ChargeSubType>SUb2_1</ChargeSubType>
               <CHARGECATTOTALAMOUNT>SUB2_1</CHARGECATTOTALAMOUNT>
          </EstChargeCatTotal>
          <EstChargeCatTotal>
               <ChargeSubType>SUb2_2</ChargeSubType>
               <CHARGECATTOTALAMOUNT>SUB2_2</CHARGECATTOTALAMOUNT>
          </EstChargeCatTotal>
     </ChargeCatTotalCollection>
     </EstTotals>
     <EstimateLines>
          <EstimateLine LINENUMBER="1">
               <LineBlockID>LN2_1</LineBlockID>
               <LaborHours>12.09</LaborHours>
          </EstimateLine>
               <EstimateLine LINENUMBER="2">
               <LineBlockID>LN2_2</LineBlockID>
          <LaborHours>3.56</LaborHours>
     </EstimateLine>
     </EstimateLines>
</VehEstimate>
</EstList>
</EstimateComp>
<AdminComp>
<ClaimInformation>
<ClaimNumber>2345</ClaimNumber>
</ClaimInformation>
</AdminComp>
</PwayWorkfile>

Note : Not all the xml tags are included, as this sample is provided to convey the structure of the XML*

Heres how i am trying to create the index
==========================================
CREATE INDEX ESTLINE_XMLINDEX ON PWAYWORKFILE_TABLE (WORKFILE) INDEXTYPE IS XDB.XMLINDEX
PARAMETERS (
'GROUP GRP_ESTLINE_XMLINDEX
XMLTABLE GRP_ESTLINE_XMLINDEX_TAB1
XMLNAMESPACES(DEFAULT ''http://www.cccis.com/Pathways/Workfile''),
''/PwayWorkfile''
COLUMNS
CURESTID VARCHAR2(100) PATH ''EstimateComp/LastOutboxEstID'',
CLAIMNUMBER VARCHAR2(100) PATH ''AdminComp/ClaimInformation/ClaimNumber'',
Est_Fragment XMLTYPE PATH ''EstimateComp/EstList/VehEstimate'' VIRTUAL
XMLTABLE GRP_ESTLINE_XMLINDEX_TAB2
XMLNAMESPACES(DEFAULT ''http://www.cccis.com/Pathways/Workfile''),
''/VehEstimate'' PASSING Est_Fragment
COLUMNS
NUMBERLINES VARCHAR(100) PATH ''@NumberLines'',
LINEESTIMATEID VARCHAR(100) PATH ''EstimateID'',
Chrge_Fragment   XMLTYPE     PATH ''EstTotals/ChargeCatTotalCollection/EstChargeCatTotal'' VIRTUAL,  --1
EstLine_Fragment        XMLTYPE     PATH ''EstimateLines/EstimateLine'' VIRTUAL               --2       (Error for 2 Virtual columns)
XMLTABLE GRP_ESTLINE_XMLINDEX_TAB3
XMLNAMESPACES(DEFAULT ''http://www.cccis.com/Pathways/Workfile''),
''/EstChargeCatTotal'' PASSING Chrge_Fragment
COLUMNS
CHARGESUBTYPE VARCHAR(100) PATH ''ChargeSubType'',
CHARGECATTOTALAMOUNT VARCHAR(100) PATH ''Amount''
XMLTABLE GRP_ESTLINE_XMLINDEX_TAB4
XMLNAMESPACES(DEFAULT ''http://www.cccis.com/Pathways/Workfile''),
''/EstimateLine'' PASSING EstLine_Fragment
COLUMNS
LINENUMBER VARCHAR(100) PATH ''@LineNumber'',
LINEBLOCKID VARCHAR(100) PATH ''LineBlockID'',
LABORCATEGORY VARCHAR (100) PATH ''LaborCategory'',
LABORHOURS VARCHAR (100) PATH ''LaborHours'',
LINEOPERATION VARCHAR (100) PATH ''LineOperation'',
MANUALLABORINCLUDE VARCHAR (100) PATH ''ManualLaborInclude'',
MANUALPAINTINCLUDE VARCHAR (100) PATH ''ManualPaintInclude'',
MANUALPRICEINCLUDE VARCHAR (100) PATH ''ManualPriceInclude'',
PAINTHOURS VARCHAR (100) PATH ''PaintHours'',
PARTPRICE VARCHAR (100) PATH ''PartPrice'',
PARTQUANTITY VARCHAR (100) PATH ''PartQuantity'',
PARTTYPE VARCHAR (100) PATH ''PartType'',
ADJUSTTYPE VARCHAR (100) PATH ''DataList/EstLineData/PriceAdjustment/AdjustType'',
LINEMODIFYESTID VARCHAR (100) PATH ''DataList/EstLineData/LineModifyEstID'',
PARTCHARGECATEGORY VARCHAR (100) PATH ''DataList/EstLineData/PartChargeCategory'',
PREADJPRICE VARCHAR (100) PATH 'DataList/EstLineData/PreAdjPrice'',
LABORUSERINCL VARCHAR (100) PATH 'DataList/DBMotorData/LaborInclType/@LABORUSERINCL'',
UNISIDESECTION VARCHAR (100) PATH 'DataList/DBMotorData/PriceInclType/@UNISIDESECTION'',
ADJUSTMENTAMOUNT VARCHAR (100) PATH ''Calculation/AdjustmentAmount'',
DISPLAYPRICE VARCHAR (100) PATH 'Calculation/DisplayPrice'',
ACTUALPRICE VARCHAR (100) PATH 'Calculation/ActualPrice'',
GENERICLINESOURCE VARCHAR (100) PATH ''GenericLineSource'',
OVERALLMARKUPAMOUNT VARCHAR (100) PATH ''Calculation/OverallMarkupAmount'',
OVERALLDISCOUNTAMOUNT VARCHAR (100) PATH ''Calculation/OverallDiscountAmount'',
LABORAMOUNT VARCHAR (100) PATH 'Calculation/LaborAmount'',
PAINTLABORAMOUNT VARCHAR (100) PATH ''Calculation/PaintLaborAmount'',
BODYSUPPLYHOURS VARCHAR (100) PATH 'Calculation/BodySupplyHours'',
BODYSUPPLYAMOUNT VARCHAR (100) PATH ''Calculation/BodySupplyAmount'',
PAINTSUPPLYHOURS VARCHAR (100) PATH ''Calculation/PaintSupplyHours'',
PAINTSUPPLYAMOUNT VARCHAR (100) PATH ''Calculation/PaintSupplyAmount'',
PAINTINCLUDETYPENONE VARCHAR (100) PATH ''DataList/DBMotorPaintData/PaintInclType/@NONE'',
PRICEINCLTYPENONE VARCHAR (100) PATH ''DataList/DBMotorData/PriceInclType/@NONE'',
LABORINCLTYPENONE VARCHAR (100) PATH ''DataList/DBMotorData/LaborInclType/@NONE'',
LABORADJUSTMENTFLAG VARCHAR (100) PATH ''Calculation/LaborAdjustmentFlag'',
PAINTOHINCL VARCHAR (100) PATH 'DataList/DBMotorPaintData/PaintInclType/@PAINTOHINCL'',
PAINTOLINCL VARCHAR (100) PATH 'DataList/DBMotorPaintData/PaintInclType/@PAINTOLINCL'',
AMASEMBLY INTEGER PATH ''DataList/AMAssembly'',
AMASEMBLY_DEALERID VARCHAR (100) PATH ''DataList/AMAssembly/DealerID'',
AMPARTSTATUS VARCHAR (100) PATH ''DataList/AMAssembly/AmPartStatus'',
DATABASEPRICE_AMT VARCHAR (100) PATH ''DataList/DBMotorData/MotorPartPrice'',
OVERALLMARKUP_PCT VARCHAR (100) PATH ''Calculation/OverallMarkupPercentage'',
OVERALLDISCOUNT_PCT VARCHAR (100) PATH ''Calculation/OverallDiscountPercentage'',
OEMPRICE VARCHAR (100) PATH ''Calculation/OEMPrice'',
SINGLEOHINCL VARCHAR (100) PATH ''DataList/DBMotorData/LaborInclType/@SINGLEOHINCL'',
DOUBLEOHINCL VARCHAR (100) PATH ''DataList/DBMotorData/LaborInclType/@DOUBLEOHINCL'',
PPAGEINCL VARCHAR (100) PATH ''DataList/DBMotorData/LaborInclType/@PPAGEINCL'',
SUPPLIERID VARCHAR (100) PATH ''DataList/RPSPart/SupplierID''
');

Heres the select query which i want the xmlindex to work for
==============================================================
SELECT
all the fields except the xml fragments-- all the fields are not given to save some space
from PWAYWORKFILE_TABLE,
xmltable(
xmlnamespaces(DEFAULT 'http://www.cccis.com/Pathways/Workfile'
, 'http://www.cccis.com/Pathways/CommonType' as "pct")
, '$XS/PwayWorkfile' passing WORKFILE as "XS"
columns
ClaimNumber VARCHAR2(100) PATH 'AdminComp/ClaimInformation/ClaimNumber',
CURESTID VARCHAR2(100) PATH 'EstimateComp/LastOutboxEstID',
Est_Fragment XMLTYPE PATH 'EstimateComp/EstList/VehEstimate'
) m,
XMLTABLE(
xmlnamespaces(DEFAULT 'http://www.cccis.com/Pathways/Workfile'
, 'http://www.cccis.com/Pathways/CommonType' as "pct")
, '$PY/VehEstimate' passing m.Est_Fragment as "PY"
columns
NUMBERLINES VARCHAR(100) PATH '@NumberLines',
LINEESTIMATEID VARCHAR(100) PATH 'EstimateID',
Chrge_Fragment XMLTYPE PATH 'EstTotals/ChargeCatTotalCollection/EstChargeCatTotal',
EstLine_Fragment XMLTYPE PATH 'EstimateLines/EstimateLine'
) l,
XMLTABLE(
xmlnamespaces(DEFAULT 'http://www.cccis.com/Pathways/Workfile'
, 'http://www.cccis.com/Pathways/CommonType' as "pct")
, '$CY/EstChargeCatTotal' passing l.Chrge_Fragment as "CY"
columns
CHARGESUBTYPE VARCHAR(100) PATH 'ChargeSubType',
CHARGECATTOTALAMOUNT VARCHAR(100) PATH 'Amount'
) k,
XMLTABLE(
xmlnamespaces(DEFAULT 'http://www.cccis.com/Pathways/Workfile'
, 'http://www.cccis.com/Pathways/CommonType' as "pct")
, '$NY/EstimateLine' passing l.EstLine_Fragment as "NY"
columns
LINENUMBER VARCHAR(100) PATH '@LineNumber',
LINEBLOCKID VARCHAR(100) PATH 'LineBlockID',
LABORCATEGORY VARCHAR (100) PATH 'LaborCategory',
LABORHOURS VARCHAR (100) PATH 'LaborHours',
LINEOPERATION VARCHAR (100) PATH 'LineOperation',
MANUALLABORINCLUDE VARCHAR (100) PATH 'ManualLaborInclude',
MANUALPAINTINCLUDE VARCHAR (100) PATH 'ManualPaintInclude',
MANUALPRICEINCLUDE VARCHAR (100) PATH 'ManualPriceInclude',
PAINTHOURS VARCHAR (100) PATH 'PaintHours',
PARTPRICE VARCHAR (100) PATH 'PartPrice',
PARTQUANTITY VARCHAR (100) PATH 'PartQuantity',
PARTTYPE VARCHAR (100) PATH 'PartType',
ADJUSTTYPE VARCHAR (100) PATH 'DataList/EstLineData/PriceAdjustment/AdjustType',
LINEMODIFYESTID VARCHAR (100) PATH 'DataList/EstLineData/LineModifyEstID',
PARTCHARGECATEGORY VARCHAR (100) PATH 'DataList/EstLineData/PartChargeCategory',
PREADJPRICE VARCHAR (100) PATH 'DataList/EstLineData/PreAdjPrice',
LABORUSERINCL VARCHAR (100) PATH 'DataList/DBMotorData/LaborInclType/@LABORUSERINCL',
UNISIDESECTION VARCHAR (100) PATH 'DataList/DBMotorData/PriceInclType/@UNISIDESECTION',
ADJUSTMENTAMOUNT VARCHAR (100) PATH 'Calculation/AdjustmentAmount',
DISPLAYPRICE VARCHAR (100) PATH 'Calculation/DisplayPrice',
ACTUALPRICE VARCHAR (100) PATH 'Calculation/ActualPrice',
GENERICLINESOURCE VARCHAR (100) PATH 'GenericLineSource',
OVERALLMARKUPAMOUNT VARCHAR (100) PATH 'Calculation/OverallMarkupAmount',
OVERALLDISCOUNTAMOUNT VARCHAR (100) PATH 'Calculation/OverallDiscountAmount',
LABORAMOUNT VARCHAR (100) PATH 'Calculation/LaborAmount',
PAINTLABORAMOUNT VARCHAR (100) PATH 'Calculation/PaintLaborAmount',
BODYSUPPLYHOURS VARCHAR (100) PATH 'Calculation/BodySupplyHours',
BODYSUPPLYAMOUNT VARCHAR (100) PATH 'Calculation/BodySupplyAmount',
PAINTSUPPLYHOURS VARCHAR (100) PATH 'Calculation/PaintSupplyHours',
PAINTSUPPLYAMOUNT VARCHAR (100) PATH 'Calculation/PaintSupplyAmount',
PAINTINCLUDETYPENONE VARCHAR (100) PATH 'DataList/DBMotorPaintData/PaintInclType/@NONE',
PRICEINCLTYPENONE VARCHAR (100) PATH 'DataList/DBMotorData/PriceInclType/@NONE',
LABORINCLTYPENONE VARCHAR (100) PATH 'DataList/DBMotorData/LaborInclType/@NONE',
LABORADJUSTMENTFLAG VARCHAR (100) PATH 'Calculation/LaborAdjustmentFlag',
PAINTOHINCL VARCHAR (100) PATH 'DataList/DBMotorPaintData/PaintInclType/@PAINTOHINCL',
PAINTOLINCL VARCHAR (100) PATH 'DataList/DBMotorPaintData/PaintInclType/@PAINTOLINCL',
AMASEMBLY INTEGER PATH 'exists(DataList/AMAssembly)',
AMASEMBLY_DEALERID VARCHAR (100) PATH 'DataList/AMAssembly/DealerID',
AMPARTSTATUS VARCHAR (100) PATH 'DataList/AMAssembly/AmPartStatus',
DATABASEPRICE_AMT VARCHAR (100) PATH 'DataList/DBMotorData/MotorPartPrice',
OVERALLMARKUP_PCT VARCHAR (100) PATH 'Calculation/OverallMarkupPercentage',
OVERALLDISCOUNT_PCT VARCHAR (100) PATH 'Calculation/OverallDiscountPercentage',
OEMPRICE VARCHAR (100) PATH 'Calculation/OEMPrice',
SINGLEOHINCL VARCHAR (100) PATH 'DataList/DBMotorData/LaborInclType/@SINGLEOHINCL',
DOUBLEOHINCL VARCHAR (100) PATH 'DataList/DBMotorData/LaborInclType/@DOUBLEOHINCL',
PPAGEINCL VARCHAR (100) PATH 'DataList/DBMotorData/LaborInclType/@PPAGEINCL',
SUPPLIERID VARCHAR (100) PATH 'DataList/RPSPart/SupplierID'
)j;

Thanks,
Arghyadip
  • 1. Re: Help on SXI Getting this errors ORA-30986, ORA-29896, ORA-30959
    beta32c Newbie
    Currently Being Moderated
    Sorry i forgot to mention my Oracle version
    here it it

    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE 11.2.0.3.0 Production
  • 2. Re: Help on SXI Getting this errors ORA-30986, ORA-29896, ORA-30959
    odie_63 Guru
    Currently Being Moderated
    a) I can't create two virtual columns at the same level (ORA-30986: XML Index with more than one virtual column is disallowed), although i am reading two xml sequences from the same level (EstChargeCatTotal and EstimateLine) , how can i have index on both the XML fragments?
    The manual says :
    You cannot create more than one XMLType column in a given XMLTable clause. To achieve that effect, you must instead define an additional group.
    http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_indexing.htm#BCGHGIGC

    Some examples here :

    http://www.liberidu.com/blog/2010/01/17/structured-xmlindex-part-3-%E2%80%93-building-multiple-xmlindex-structures/

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points