My Oracle Support Banner

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
  Symptoms
  Cause
  Solution
  References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.