ORA-29036 when a function-based index is created with extractValue on XML SB table (Doc ID 1361875.1)

Last updated on SEPTEMBER 26, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.1 to 11.2.0.1 - Release: 11.2 to 11.2
Information in this document applies to any platform.

Symptoms

We are facing issues in creating index on XMLTYPE elements. In the below example, when we try to create index on "/PurchaseOrder/LineItems/LineItem/Description" element, its throwing "ORA-29036: This feature is not supported" error. It is allowing us to create index on Description element if we remove "maxOccurs="unbounded" attribute either in "LineItem" element or "Part" element.

Below are the sample code for schema, table and index creation.

set serveroutput on

DECLARE
XMLSCHEMADOC VARCHAR2(32767);
BEGIN

XMLSCHEMADOC := '
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb"
version="1.0" xdb:storeVarrayAsTable="true">
<xs:element name="PurchaseOrder" type="PurchaseOrderType"/>
<xs:complexType name="PurchaseOrderType">
<xs:sequence>
<xs:element name="Reference" type="ReferenceType"/>
<xs:element name="Actions" type="ActionsType"/>
<xs:element name="Reject" type="RejectionType" minOccurs="0"/>
<xs:element name="Requestor" type="RequestorType"/>
<xs:element name="User" type="UserType"/>
<xs:element name="CostCenter" type="CostCenterType"/>
<xs:element name="ShippingInstructions" type="ShippingInstructionsType"/>
<xs:element name="SpecialInstructions" type="SpecialInstructionsType"/>
<xs:element name="LineItems" type="LineItemsType"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="LineItemsType">
<xs:sequence>
<xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="LineItemType">
<xs:sequence>
<xs:element name="Description" type="DescriptionType"/>
<xs:element name="Part" type="PartType" maxOccurs="unbounded"/>
</xs:sequence>
<xs:attribute name="ItemNumber" type="xs:integer"/>
</xs:complexType>
<xs:complexType name="PartType">
<xs:attribute name="Id">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:minLength value="10"/>
<xs:maxLength value="14"/>
</xs:restriction>
</xs:simpleType>
</xs:attribute>
<xs:attribute name="Quantity" type="moneyType"/>
<xs:attribute name="UnitPrice" type="quantityType"/>
</xs:complexType>
<xs:simpleType name="ReferenceType">
<xs:restriction base="xs:string">
<xs:minLength value="18"/>
<xs:maxLength value="30"/>
</xs:restriction>
</xs:simpleType>
<xs:complexType name="ActionsType">
<xs:sequence>
<xs:element name="Action" maxOccurs="4">
<xs:complexType>
<xs:sequence>
<xs:element name="User" type="UserType"/>
<xs:element name="Date" type="DateType" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
<xs:complexType name="RejectionType">
<xs:all>
<xs:element name="User" type="UserType" minOccurs="0"/>
<xs:element name="Date" type="DateType" minOccurs="0"/>
<xs:element name="Comments" type="CommentsType" minOccurs="0"/>
</xs:all>
</xs:complexType>
<xs:complexType name="ShippingInstructionsType">
<xs:sequence>
<xs:element name="name" type="NameType" minOccurs="0"/>
<xs:element name="address" type="AddressType" minOccurs="0"/>
<xs:element name="telephone" type="TelephoneType" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:simpleType name="moneyType">
<xs:restriction base="xs:decimal">
<xs:fractionDigits value="2"/>
<xs:totalDigits value="12"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="quantityType">
<xs:restriction base="xs:decimal">
<xs:fractionDigits value="4"/>
<xs:totalDigits value="8"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="UserType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="10"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="RequestorType">
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="128"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="CostCenterType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="4"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="VendorType">
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="20"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="PurchaseOrderNumberType">
<xs:restriction base="xs:integer"/>
</xs:simpleType>
<xs:simpleType name="SpecialInstructionsType">
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="2048"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="NameType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="20"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="AddressType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="256"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="TelephoneType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="24"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="DateType">
<xs:restriction base="xs:date"/>
</xs:simpleType>
<xs:simpleType name="CommentsType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="2048"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="DescriptionType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="256"/>
</xs:restriction>
</xs:simpleType>
</xs:schema>
';

DBMS_XMLSCHEMA.registerSchema(
SCHEMAURL => 'purchaseorder1.xsd',
SCHEMADOC => XMLSCHEMADOC,
LOCAL => TRUE,
GENTYPES => TRUE,
GENTABLES => TRUE,
genbean => FALSE,
enablehierarchy => 1,
force => FALSE);
END;
/

PL/SQL procedure successfully completed.

CREATE TABLE PURCHASEORDER (
PURCHASE_ORDER_ID NUMBER PRIMARY KEY,
PURCHASE_ORDER_CONTENTS XMLType
)
XMLTYPE COLUMN PURCHASE_ORDER_CONTENTS
XMLSCHEMA "purchaseorder1.xsd"
ELEMENT "PurchaseOrder"
VARRAY PURCHASE_ORDER_CONTENTS."XMLDATA"."LineItems"."LineItem"
STORE AS table ACTION_TABLE
((primary key (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))
organization index overflow);

Table created.

create index po_lineitem_org_idx on PURCHASEORDER(extractValue(PURCHASE_ORDER_CONTENTS,'/PurchaseOrder/LineItems/LineItem/Description'));


create index po_lineitem_org_idx on PURCHASEORDER(extractValue(PURCHASE_ORDER_CONTENTS,'/PurchaseOrder/LineItems/LineItem/Description'))
*
ERROR at line 1:
ORA-29036: This feature is not supported

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms