Below is our DB version.
SQL*Plus: Release 11.2.0.1 - Production on Tue Nov 29 17:45:43 2011
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Hi,
We are trying to query Binary XML Table using some columns on Structured XMLIndex.
Query does not return any data rows. Trying to find out what could be wrong.
Given below is DDL used to setup Binary XML Table and related XMLIndex for structured searches.
Initially we got ORA-29896 and so we had to split our columns
part of Structured XMLIndex into multiple groups -
1) to avoid ORA-29896 and
2) further had Multiple Collections to be searched on
(There is limit of having only 1 XMLType in each XMLIndex Parameter Group)
and hence anyway we had to use several ALTER XMLIndex statements - one per Parameter group.
DDL used to create Binary XML Table and XMLIndex is as shown below:
CREATE TABLE TNMAB_OCEAN_RATE_XML (
OCEAN_RATE_XML XMLType NOT NULL,
CREATE_BY VARCHAR2(15),
CREATE_DT_GMT TIMESTAMP(6),
CREATE_CLIENT_ID VARCHAR2(65),
UPDATE_BY VARCHAR2(15),
UPDATE_DT_GMT TIMESTAMP(6),
UPDATE_CLIENT_ID VARCHAR2(65),
PIC_VERSION_NUM NUMBER(20))
TABLESPACE TNMAB_XMLDB_ME_DATA XMLTYPE "OCEAN_RATE_XML"
STORE AS BINARY XML
/
Table created.
CREATE INDEX TNMAB_OCEAN_RATE_XML_XDX1 ON TNMAB_OCEAN_RATE_XML(OCEAN_RATE_XML)
INDEXTYPE IS XDB.XMLINDEX
PARAMETERS (
'GROUP TNMAB_OCEANRATE_XML_XDX_GRP1
XMLTABLE TNMAB_OCEAN_RATE_XML_XTAB1
XMLNAMESPACES(default ''http://com.oocl.schema.tnm.agreementbuilder''),
''/OceanOfferRate''
COLUMNS
OBJECT_ID NUMBER(20) PATH ''ObjectID'',
AGREEMENT_ID NUMBER(20) PATH ''AgreementID'',
TRADE_LANE VARCHAR2(3) PATH ''TradeLane'',
DIRECTION VARCHAR2(1) PATH ''Direction'',
REF_RATE_ID NUMBER(20) PATH ''GuidelineRateReference/Trunk/RateID'',
CREATED_ON TIMESTAMP PATH ''CreatedOn'',
FLAGS NUMBER(10) PATH ''Flags'',
ORIGINS XMLType PATH ''Origins/ID_Wrappers'' VIRTUAL
XMLTABLE TNMAB_OCEAN_RATE_XML_XTAB2
XMLNAMESPACES(default ''http://com.oocl.schema.tnm.agreementbuilder''),
''/ID_Wrappers'' PASSING ORIGINS
COLUMNS
ORIGIN_ID NUMBER(15) PATH ''Value''')
/
Index created.
ALTER INDEX TNMAB_OCEAN_RATE_XML_XDX1
PARAMETERS ('ADD_GROUP GROUP TNMAB_OCEANRATE_XML_XDX_GRP2
XMLTABLE TNMAB_OCEAN_RATE_XML_XTAB3
XMLNAMESPACES(default ''http://com.oocl.schema.tnm.agreementbuilder''),
''/OceanOfferRate''
COLUMNS
CARGO_NATURE NUMBER(3) PATH ''Commodity/CargoNatureGroup'',
COMMODITY_GROUP_ID NUMBER(20) PATH ''Commodity/CommodityGroupID'',
RATE_ID NUMBER(20) PATH ''RateID'',
RATE_STATUS VARCHAR2(20) PATH ''RateStatus'',
SALES_OFCE_CDE VARCHAR2(3) PATH ''SalesOffice'',
CREATED_BY VARCHAR2(10) PATH ''CreatedBy'',
DESTINATIONS XMLType PATH ''Destinations/ID_Wrappers'' VIRTUAL
XMLTABLE TNMAB_OCEAN_RATE_XML_XTAB4
XMLNAMESPACES(default ''http://com.oocl.schema.tnm.agreementbuilder''),
''/ID_Wrappers'' PASSING DESTINATIONS
COLUMNS
DESTINATION_ID NUMBER(15) PATH ''Value'''
)
/
Index altered.
Please note following SQL Query works fine as expected.
Referenced AGREEMENT_ID is part of the first XMLIndex Parameter Group
TNMAB_OCEANRATE_XML_XDX_GRP1 and no issues.
1 row is returned as expected
SELECT XMLSerialize(document B.OCEAN_RATE_XML as CLOB) AS OI_XML
FROM TNMAB_OCEAN_RATE_XML B,
XMLTable(XMLNameSpaces(default 'http://com.oocl.schema.tnm.agreementbuilder'),'/OceanOfferRate'
PASSING B.OCEAN_RATE_XML COLUMNS
AGREEMENT_ID NUMBER(20) PATH 'AgreementID',
RATE_ID NUMBER(20) PATH 'RateID',
RATE_STATUS VARCHAR2(20) PATH 'RateStatus',
OBJECT_ID NUMBER(20) PATH 'ObjectID',
CARGO_NATURE NUMBER(3) PATH 'Commodity/CargoNatureGroup',
SALES_OFFICE VARCHAR2(3) PATH 'SalesOffice',
TRADE_LANE VARCHAR2(3) PATH 'TradeLane',
DIRECTION VARCHAR2(1) PATH 'Direction',
LAST_UPDATED_BY VARCHAR2(10) PATH 'LastUpdatedBy',
LAST_UPDATED_ON TIMESTAMP PATH 'LastUpdated',
CREATED_ON TIMESTAMP PATH 'CreatedOn',
EFFECTIVE_FROM TIMESTAMP PATH 'EffectiveFrom',
EFFECTIVE_TO TIMESTAMP PATH 'EffectiveTo',
LAST_SENT_ON TIMESTAMP PATH 'LastSentDate',
VALIDITY_EXPIRY_DT TIMESTAMP PATH 'ValidityExpirationDate',
FLAGS NUMBER(10) PATH 'Flags' ) QITAB
WHERE QITAB.AGREEMENT_ID=2
1 row selected
SQL>
However, when we use structured Column RATE_ID defined as part of
second XMLIndex Group (TNMAB_OCEANRATE_XML_XDX_GRP2)
Query does not return any data rows. There are 38 data rows satisfying this search.
SELECT XMLSerialize(document B.OCEAN_RATE_XML as CLOB) AS OI_XML
FROM TNMAB_OCEAN_RATE_XML B,
XMLTable(XMLNameSpaces(default 'http://com.oocl.schema.tnm.agreementbuilder'),'/OceanOfferRate'
PASSING B.OCEAN_RATE_XML COLUMNS
AGREEMENT_ID NUMBER(20) PATH 'AgreementID',
RATE_ID NUMBER(20) PATH 'RateID',
RATE_STATUS VARCHAR2(20) PATH 'RateStatus',
OBJECT_ID NUMBER(20) PATH 'ObjectID',
CARGO_NATURE NUMBER(3) PATH 'Commodity/CargoNatureGroup',
SALES_OFFICE VARCHAR2(3) PATH 'SalesOffice',
TRADE_LANE VARCHAR2(3) PATH 'TradeLane',
DIRECTION VARCHAR2(1) PATH 'Direction',
LAST_UPDATED_BY VARCHAR2(10) PATH 'LastUpdatedBy',
LAST_UPDATED_ON TIMESTAMP PATH 'LastUpdated',
CREATED_ON TIMESTAMP PATH 'CreatedOn',
EFFECTIVE_FROM TIMESTAMP PATH 'EffectiveFrom',
EFFECTIVE_TO TIMESTAMP PATH 'EffectiveTo',
LAST_SENT_ON TIMESTAMP PATH 'LastSentDate',
VALIDITY_EXPIRY_DT TIMESTAMP PATH 'ValidityExpirationDate',
FLAGS NUMBER(10) PATH 'Flags' ) QITAB
WHERE QITAB.RATE_ID=2
0 rows selected
SQL>
Why is no data returned for the second query?
We are totally confused.
Now, one more interesting observation. When we
include RATE_ID in the SELECT clause then query appears to work fine
and 38 data rows are returned !
This is very strange behaviour. How is Oracle able to properly return data
when column being searched is part of SELECT llist. Any insight or clues on
this behaviour would be greatly and truly appreciated.
SELECT XMLSerialize(document B.OCEAN_RATE_XML as CLOB) AS OI_XML, QITAB.RATE_ID
FROM TNMAB_OCEAN_RATE_XML B,
XMLTable(XMLNameSpaces(default 'http://com.oocl.schema.tnm.agreementbuilder'),'/OceanOfferRate'
PASSING B.OCEAN_RATE_XML COLUMNS
AGREEMENT_ID NUMBER(20) PATH 'AgreementID',
RATE_ID NUMBER(20) PATH 'RateID',
RATE_STATUS VARCHAR2(20) PATH 'RateStatus',
OBJECT_ID NUMBER(20) PATH 'ObjectID',
CARGO_NATURE NUMBER(3) PATH 'Commodity/CargoNatureGroup',
SALES_OFFICE VARCHAR2(3) PATH 'SalesOffice',
TRADE_LANE VARCHAR2(3) PATH 'TradeLane',
DIRECTION VARCHAR2(1) PATH 'Direction',
LAST_UPDATED_BY VARCHAR2(10) PATH 'LastUpdatedBy',
LAST_UPDATED_ON TIMESTAMP PATH 'LastUpdated',
CREATED_ON TIMESTAMP PATH 'CreatedOn',
EFFECTIVE_FROM TIMESTAMP PATH 'EffectiveFrom',
EFFECTIVE_TO TIMESTAMP PATH 'EffectiveTo',
LAST_SENT_ON TIMESTAMP PATH 'LastSentDate',
VALIDITY_EXPIRY_DT TIMESTAMP PATH 'ValidityExpirationDate',
FLAGS NUMBER(10) PATH 'Flags' ) QITAB
WHERE QITAB.RATE_ID=2
38 rows returned
SQL>
Thanks to all for help in advance.
Auro