2 Replies Latest reply: Mar 12, 2013 9:00 AM by odie_63 RSS

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

    beta32c
      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
          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
            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/