1 2 3 Previous Next 38 Replies Latest reply: Jun 29, 2012 6:43 AM by MichaelR64 Go to original post RSS
      • 15. Re: How to create the proper index for my xml..
        Marco Gralike
        Marc,

        the ordinality part is only applicable to the standard XMLTABLE query but alas can not me used (apparently, as mentioned by you, that is maybe a bug?) in the create structured XMLIndex syntax.

        Does that help (or am I missing something?)

        M.
        • 16. Re: How to create the proper index for my xml..
          Marco Gralike
          In general I solve the multi-item sequence by checking where there are "occurence unbouded" issues in the XML schema / XML document (aka not 1 to 1 branches), split the XMLTYPE result on the correct XML level by passing it on to the next nested XMLTABLE construct and/or add the FOR ORDINALITY clause to create unique row values for use in the "upper" XMLTABLE construct. I have never applied to latter (or tested yet), for ordinality, in the case of structured XMLIndexes. If it is not allowed I would break the issue up in multiple XMLIndex / XMLTable (content table) constructs.
          • 17. Re: How to create the proper index for my xml..
            MichaelR64
            Okay ..... i think ....
            hmmm not sure i can follow all of this....

            Just to stress this, the problem is somewhere in the index spec.
            I can create the index when there are files in the table. i just can not create any new files in the table then after creation.
            The error occurs when trying to add the file to the table, not when querying the table.

            Querying runs fine.

            From what i have found (just a quick ggle) FOR ORDINALITY seems to refer to columns in the context XMLTable queries.
            And as i said the error occurs when trying to add files to the table.

            Tia

            Edited by: MichaelR64 on 10-okt-2011 13:43
            • 18. Re: How to create the proper index for my xml..
              Marco Gralike
              The extra rows probably force the multi-item sequence issue due to more values then expected. Elements in there apparently dont look to have the problem.
              • 19. Re: How to create the proper index for my xml..
                MichaelR64
                Hmm,

                What do you mean with extra rows ?
                All of my rows in the table have the same attachment.
                I can store any number of attachments, switch on the index, and upon further inserts the error appears when inserting attachments.
                The attachments already in there do appear to be indexed.

                But perhaps i don't understand you very well..

                Upon further testing i find that it is this bit in the Index:
                         , header1    varchar2(30) path 'Table/Row[1]/Cell[1]/Data/text()'
                         , header2    varchar2(30) path 'Table/Row[1]/Cell[2]/Data/text()'
                that is giving the error. removing that stops the error from happening at inserts..
                • 20. Re: How to create the proper index for my xml..
                  Marco Gralike
                  This is a bit a gut feeling but it seems that if you insert data that cause the "multi value" error

                  a) insert all "multi-value" documents, then create the index --> no problem
                  b) add extra "multi-value" --> error

                  I would have expected

                  1) no rows, then create the index --> no problem --> add "multi-value" docs --> error
                  2) or insert "multi-value" docs --> create the index --> error

                  M.
                  • 21. Re: How to create the proper index for my xml..
                    odie_63
                    MichaelR64 wrote:
                    Upon further testing i find that it is this bit in the Index:
                    , header1    varchar2(30) path 'Table/Row[1]/Cell[1]/Data/text()'
                    , header2    varchar2(30) path 'Table/Row[1]/Cell[2]/Data/text()'
                    that is giving the error. removing that stops the error from happening at inserts..
                    That's what I noticed too.

                    I don't like calling it a bug so quickly, but IMO the error doesn't make sense.

                    If we consider this part is the problem :
                    ...
                       , '/Workbook/Worksheet'
                       passing object_value
                       columns header1    varchar2(30) path 'Table/Row[1]/Cell[1]/Data/text()'
                    ...
                    that means either there are multiple occurrences of Table under the same Worksheet, or the XPath expression for header1 is applied on multiple Worksheets instead of only the context item.
                    The former is impossible, so it seems there's something strange happening here.

                    As a "possible" workaround, using a position predicate on Table element :
                    CREATE INDEX test_xml2003_ix3 ON test_xml2003 (object_value) INDEXTYPE IS XDB.XMLIndex
                    PARAMETERS (
                    q'#
                       XMLTable my_tab1
                         XMLNamespaces(
                           'urn:schemas-microsoft-com:office:spreadsheet' as "ss"
                         , default 'urn:schemas-microsoft-com:office:spreadsheet'
                         )
                       , '/Workbook/Worksheet'
                       passing object_value
                       columns header1    varchar2(30) path 'Table[1]/Row[1]/Cell[1]/Data/text()'
                             , header2    varchar2(30) path 'Table[1]/Row[1]/Cell[2]/Data/text()'
                             , rowset     xmltype      path 'Table[1]/Row' virtual
                             , sheetname  varchar2(30) path '@ss:Name'     
                       XMLTable my_tab2
                         XMLNamespaces(
                           'urn:schemas-microsoft-com:office:spreadsheet' as "ss"
                         , default 'urn:schemas-microsoft-com:office:spreadsheet'
                         )
                       , '/Row'
                       passing rowset
                       columns column1 varchar2(100)  PATH 'Cell[1]/Data/text()',
                               column2 varchar2(100)  PATH 'Cell[2]/Data/text()'
                    #'
                    );
                    
                    select x2.*
                    from test_xml2003,
                         XMLTable(
                           XMLNamespaces(
                             'urn:schemas-microsoft-com:office:spreadsheet' as "ss"
                           , default 'urn:schemas-microsoft-com:office:spreadsheet'
                           )
                         , '$d/Workbook/Worksheet[@ss:Name=$sheet]'
                         PASSING object_value as "d"
                               , 'Feuil1' as "sheet"
                         columns header1    varchar2(30) path 'Table[1]/Row[1]/Cell[1]/Data/text()',
                                 header2    varchar2(30) path 'Table[1]/Row[1]/Cell[2]/Data/text()',
                                 rowset     xmltype      path 'Table[1]/Row'
                         ) x1
                       , XMLTable(
                           XMLNamespaces(
                             'urn:schemas-microsoft-com:office:spreadsheet' as "ss"
                           , default 'urn:schemas-microsoft-com:office:spreadsheet'
                           )
                         , '/Row'
                         PASSING x1.rowset
                         columns column1 varchar2(100)  PATH 'Cell[1]/Data/text()',
                                 column2 varchar2(100)  PATH 'Cell[2]/Data/text()'
                         ) x2
                    where x1.header1 = 'COLUMN1'
                    and x1.header2 = 'COLUMN2'
                    and x2.column1 != x1.header1
                    and x2.column2 != x1.header2
                    ;
                    • 22. Re: How to create the proper index for my xml..
                      MichaelR64
                      It gets even weirder:
                      using all 3 lines with Table[1] works indeed; but also just the rowset line with Table[1] works.

                      So yes there is something weird here.
                      • 23. Re: How to create the proper index for my xml..
                        odie_63
                        Some further investigations using a 10046 trace...

                        Here's the multitable INSERT source of the error.
                        It occurs internally when we insert a row in the base table (index maintenance) :
                        INSERT ALL 
                          WHEN (T1RNO IS NOT NULL AND T2RNO IS NULL) OR (T2RNO = 1) 
                            THEN INTO DEV.MY_TAB1(KEY, OID, RID, HEADER1, HEADER2, SHEETNAME) 
                                      VALUES(T1KEY, OID, RID, T1COL1, T1COL2, T1COL3) 
                          WHEN T2RNO IS NOT NULL 
                            THEN INTO DEV.MY_TAB2(OID, RID, PKEY, COLUMN1, COLUMN2) 
                                      VALUES(OID, RID, T1KEY, T2COL1, T2COL2) 
                        SELECT /*+ NO_XMLINDEX_REWRITE */ 
                               utl_raw.concat(utl_raw.cast_to_raw(SYS_ALIAS_0.rowid),utl_raw.cast_to_raw(to_char(SYS_ALIAS_1.T1RNO, '9999999999'))) AS T1KEY
                             , SYS_ALIAS_0.sys_nc_oid$ AS OID
                             , SYS_ALIAS_0.rowid AS RID
                             , SYS_ALIAS_1.T1RNO AS T1RNO
                             , SYS_ALIAS_1.HEADER1 AS T1COL1
                             , SYS_ALIAS_1.HEADER2 AS T1COL2
                             , SYS_ALIAS_1.SHEETNAME AS T1COL3
                             , SYS_ALIAS_2.T2RNO AS T2RNO
                             , SYS_ALIAS_2.COLUMN1 AS T2COL1
                             , SYS_ALIAS_2.COLUMN2 AS T2COL2 
                        FROM DEV."TEST_XML2003" SYS_ALIAS_0
                           , XMLTABLE( 
                               XMLNamespaces('urn:schemas-microsoft-com:office:spreadsheet' as "ss", default 'urn:schemas-microsoft-com:office:spreadsheet')
                               ,'/Workbook/Worksheet' 
                               PASSING SYS_ALIAS_0.SYS_NC00004$ 
                               COLUMNS HEADER1 varchar2(4000) PATH 'Table/Row[1]/Cell[1]/Data/text()'
                                      ,HEADER2 varchar2(4000) PATH 'Table/Row[1]/Cell[2]/Data/text()'
                                      ,ROWSET xmltype       PATH 'Table/Row'
                                      ,SHEETNAME varchar2(4000) PATH '@ss:Name'
                                      , T1RNO for ordinality 
                             ) SYS_ALIAS_1 
                             LEFT OUTER JOIN 
                             XMLTABLE( 
                               XMLNamespaces('urn:schemas-microsoft-com:office:spreadsheet' as "ss", default 'urn:schemas-microsoft-com:office:spreadsheet')
                               ,'/Row' 
                               PASSING SYS_ALIAS_1.ROWSET 
                               COLUMNS COLUMN1 varchar2(4000) PATH 'Cell[1]/Data/text()'
                                      ,COLUMN2 varchar2(4000) PATH 'Cell[2]/Data/text()'
                                      , T2RNO for ordinality 
                             ) SYS_ALIAS_2 ON (1 = 1) 
                        where SYS_ALIAS_0.rowid = :1
                        • 25. Re: How to create the proper index for my xml..
                          Marco Gralike
                          The to_char looked dangerous to me, but I am guessing we will never see that hardcoded upper limit reached. Although...its only 10 digits...Hmmm?
                          [oracle@localhost ~]$ sqlplus / as sysdba
                          
                          SQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 10 15:23:56 2011
                          
                          Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                          
                          
                          Connected to:
                          Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
                          With the Partitioning, OLAP, Data Mining and Real Application Testing options
                          
                          SQL> select to_char(10000000000,'9999999999') chars from dual;
                          
                          CHARS
                          -----------
                          ###########
                          
                          SQL> c/100/10
                            1* select to_char(1000000000,'9999999999') chars from dual
                          
                          SQL> r
                            1* select to_char(1000000000,'9999999999') chars from dual
                          
                          CHARS
                          -----------
                           1000000000
                          If two ordinality clauses are not allowed in the same XMLTABLE construct, then I guess you know now where the "bug" comes from.

                          Edited by: Marco Gralike on Oct 11, 2011 12:26 AM
                          • 26. Re: How to create the proper index for my xml..
                            MichaelR64
                            >
                            If two ordinality clauses are not allowed in the same XMLTABLE construct, then I guess you know now where the "bug" comes from.
                            >
                            Euuh no ? Oracle made a boo-boo in the Index department ? But seriously can you elaborate on this?
                            • 27. Re: How to create the proper index for my xml..
                              odie_63
                              If two ordinality clauses are not allowed in the same XMLTABLE construct, then I guess you know now where the "bug" comes from.
                              Well, in the present case, there's one FOR ORDINALITY clause in each XMLTable, so it should work... but who knows? :)
                              • 28. Re: How to create the proper index for my xml..
                                Marco Gralike
                                Please Mark, could you send me the 10046 trace?
                                Raw output is okay, I don't need a formated one.

                                I once tried to get the official OSI XMLTABLE function implementation for software integrators whitepaper, but you had to pay for it (> 100$). A little bit to expensive for me... So I don't know if officially only 1 ordinality clause per "column" spec is allowed or not to implement. In the insert all statement both ordinality clauses are used to generate the parent and reference keys for the parent and child content tables so it would make sense to ignore or disallow any ordinality clause on the user level during structured XMLIndex creation. That said, I wonder if it would have not made more sense to generate those keys while not making use of the ordinality clauses...
                                • 29. Re: How to create the proper index for my xml..
                                  Marco Gralike
                                  All software out there has its features.

                                  XMLTABLE is an official defined W3C function. All though the flow diagram is not that clear, the reference implies a maximum of 1 regarding the use of the ordinality column

                                  http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions228.htm
                                  "FOR ORDINALITY specifies that column is to be a column of generated row numbers. There must be at most one FOR ORDINALITY clause. It is created as a NUMBER column."

                                  M