2 Replies Latest reply: Jun 24, 2013 12:05 PM by KnightOfBlueArmor RSS

    Index type created as "TABLE OF XMLTYPE?"

    KnightOfBlueArmor

      I have a crazy question.

      Is it possible to create a structured or unstructured index on a type in Oracle 11.2.0.3 created as:

      CREATE TYPE mytype AS TABLE OF XMLTYPE?
      
        • 1. Re: Index type created as "TABLE OF XMLTYPE?"
          odie_63

          You're right, crazy question

          An index, whatever its type, is designed to work on a relational or object instance column, not on a class definition.

           

          I don't know what you had in mind, but to give some sense out of your question you could imagine doing something like this :

          - create the type definition

          - create a table to hold instance of this type

          - create the index on the nested table column

           

          CREATE TYPE mytype AS TABLE OF XMLTYPE;
          
          
          CREATE TABLE mytable (myobj mytype)
            NESTED TABLE myobj STORE AS mytable$nt
            (
              XMLTYPE COLUMN column_value STORE AS SECUREFILE BINARY XML
            )
          ;
          
          
          CREATE INDEX mytable_uxi ON mytable$nt(column_value)
          INDEXTYPE IS xdb.xmlindex ;
          
          
          
          • 2. Re: Index type created as "TABLE OF XMLTYPE?"
            KnightOfBlueArmor

            That's fine.  I was using the type to "simulate" JDBC batching for a stored procedure passing XMLTypes (since the JDBC driver doesn't optimize batching for CallableStatements).  I had a feeling that was what the answer was.