This discussion is archived
2 Replies Latest reply: Jan 16, 2013 6:41 PM by user8941550 RSS

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

user8941550 Newbie
Currently Being Moderated
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..

Legend

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