2 Replies Latest reply: Jan 16, 2013 8:41 PM by user8941550 RSS

    Problem to create Explain Plan and use XML Indexes. Plz follow scenario..

    user8941550
      Hi,

      Oracle Version - Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit

      I have been able to reproduce the error as below:

      Please run the following code in Schema1:
      CREATE TABLE TNAME1
      (
         DB_ID            VARCHAR2 (10 BYTE),
         DATA_ID          VARCHAR2 (10 BYTE),
         DATA_ID2         VARCHAR2 (10 BYTE),
         IDENTIFIER1      NUMBER (19) NOT NULL,
         ID1              NUMBER (10) NOT NULL,
         STATUS1          NUMBER (10) NOT NULL,
         TIME_STAMP       NUMBER (19) NOT NULL,
         OBJECT_ID        VARCHAR2 (40 BYTE) NOT NULL,
         OBJECT_NAME      VARCHAR2 (80 BYTE) NOT NULL,
         UNIQUE_ID        VARCHAR2 (255 BYTE),
         DATA_LIVE        CHAR (1 BYTE) NOT NULL,
         XML_MESSAGE      SYS.XMLTYPE,
         ID2              VARCHAR2 (255 BYTE) NOT NULL,
         FLAG1            CHAR (1 BYTE) NOT NULL,
         KEY1             VARCHAR2 (255 BYTE),
         HEADER1          VARCHAR2 (2000 BYTE) NOT NULL,
         VERSION2         VARCHAR2 (255 BYTE) NOT NULL,
         TYPE1            VARCHAR2 (15 BYTE),
         TIMESTAMP1   TIMESTAMP (6),
         SOURCE_NUMBER    NUMBER
      )
      XMLTYPE XML_MESSAGE STORE AS BINARY XML 
      PARTITION BY RANGE (TIMESTAMP1)
         (PARTITION MAX
             VALUES LESS THAN (MAXVALUE)
             )
      NOCOMPRESS
      NOCACHE
      ENABLE ROW MOVEMENT
      /
      
      
      
      begin
      app_utils.drop_parameter('TNAME1_PAR');
      end;
      
      
      
      BEGIN
      DBMS_XMLINDEX.REGISTERPARAMETER(
      'TNAME1_PAR',
      'PATH TABLE     TNAME1_RP_PT
                                PATHS (INCLUDE (            /abc:Msg/product/productType
                                                                      /abc:Msg/Products/Owner
        ) 
                                       NAMESPACE MAPPING (     xmlns:abc="Abc:Set"
                                                         ))
      ');
      END;
      /
      
      
      
      CREATE INDEX Indx_XPATH_TNAME1
         ON "TNAME1" (XML_MESSAGE)
         INDEXTYPE IS XDB.XMLINDEX PARAMETERS ( 'PARAM TNAME1_PAR' )
      local;
      Then in Schema2, create
      create synonym TNAME1 FOR SCHEMA1.TNAME1
      SCHEMA1:
      GRant All on TNAME1 to SCHEMA2
      Now in SCHEMA2, if we try:
      Explain Plan for 
      SELECT xmltype.getclobval (XML_MESSAGE)
      FROM TNAME1 t
      WHERE XMLEXISTS (
      'declare namespace abc="Abc:Set";  /abc:Msg/product/productType= ("1", "2") '
      PASSING XML_MESSAGE);
      WE GET -> ORA-00942: table or view does not exist
      whereas this works:
      Explain Plan for 
      SELECT xmltype.getclobval (XML_MESSAGE)
      FROM TNAME1 t
      - Please tell me, what is the reason behind it and how can I overcome it. It's causing all my views based on this condition to fail in another schema i.e. not picking up the XMLIndexes.

      Also
       SELECT * from DBA_XML_TAB_COLS WHERE TABLE_NAME like 'TNAME1';
      Output is like:
      OWNER, || TABLE_NAME, || COLUMN_NAME, || XMLSCHEMA || SCHEMA_OWNER, || ELEMENT_NAME, || STORAGE_TYPE, || ANYSCHEMA, || NONSCHEMA
      SCHEMA1 || TNAME1 ||     XML_MESSAGE ||          ||          || BINARY     || NO     || YES ||
      SCHEMA1 || TNAME1 ||     SYS_NC00025$ ||          ||          || CLOB     ||     ||

      - Can I change AnySchema to YES from NO for -column_name = XML_MESSAGE ? May be that will solve my problem.
      - SYS_NC00025$ is the XML Index, Why don't I get any values for ANYSCHEMA, NONSCHEMA on it. Is this what is causing the problem.


      Kindly suggest.. Thanks..