I ran into an interesting problem with XMLDB recently, which involves modifying an existing, local, unstructured XMLIndex to include structured XMLIndex components.  Basically, it seems that you can’t register the XMLTable components with a parameter and alter the index once the partitions have been created.

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0	Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

--my_table is a range partitioned table
CREATE myindex_idx ON my_table(xml_column)

INDEXTYPE IS XDB.XMLINDEX

LOCAL

PARAMETERS('PATH TABLE my_path_table PATHS (INCLUDE (/a/b/c/d/e/f))');

-- Success

BEGIN DBMS_XMLINDEX.REGISTERPARAMETER('my_param','ADD_GROUP GROUP my_group
	XMLTABLE TABLE_1 ''//ZZZ''
	COLUMNS
		ZZZ_COLLECTION XMLTYPE PATH ''YYY'' VIRTUAL
	XMLTABLE TABLE_2 ''/YYY'' PASSING ZZZ_COLLECTION
	COLUMNS
		MY_ID VARCHAR2(32) PATH ''MY_ID''');
END;/

-- Completed

ALTER INDEX myindex_idx PARAMETERS('PARAM my_param');

SQL Error: 	ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: 	line 1,
ORA-14701: 	partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method29874.
00000 – 	“warning in the execution of ODCIINDEXALTER routine.
*Cause: 	A warning was returned from the ODCIIndexAlter routine.
*Action: 	Check to see if the routine has been coded correctly
			Check the user defined warning log tables for greater details.

After posting a comment about it on Marco Gralike’s excellent XMLDB blog, he kindly provided a solution to the problem.

An important to take note of is that the ODCIINDEXALTER documentation states that 1000 characters is the max length of the parameter string, which makes the above solution applicable only for parameter strings with a length of 1000 characters or less. If you are facing a boundary case where your parameter string is longer than 1000 characters (like me), here’s the alternative solution.

BEGIN
    DBMS_XMLINDEX.DROPPARAMETER(‘indexParam’);
END;
/

BEGIN
    DBMS_XMLINDEX.REGISTERPARAMETER(
	   ‘indexParam’,
	   ‘PATH TABLE          MY_PATH_TABLE
		PATH ID INDEX       MY_PATH_ID_IDX
		PIKEY INDEX         MY_PIKEY_IDX
		VALUE INDEX			MY_VALUE_IDX
		ORDER KEY INDEX     MY_ORDERKEY_IDX
		PATHS (INCLUDE (
			/a/b/c
			/a/b/d
			/a/e/f
			/a/e/g
			/a/h/i
			/a/j/*/k
			/a/m/*/n/o/p
			/a/m/*/n/o/q/r/s
			/a/m/*/n/o/q/r/t/u/v
			/a/m/*/n/o/q/r/w))
	GROUP group_1
		XMLTABLE MY_PARENT_LIST ”/a/1/*/2”
		COLUMNS
			MY_COLLECTION XMLTYPE    PATH ”TOP_PATH” VIRTUAL
		XMLTABLE MY_CHILD_LIST ”/TOP_PATH” PASSING MY_COLLECTION
		COLUMNS
			MY_ID VARCHAR2(32)       PATH ‘MY_ID”,
			ID_COLLECTION XMLTYPE    PATH ”ID_LIST/ID” VIRTUAL
		XMLTABLE MY_INDIVIDUAL_LIST ”/ID” PASSING ID_COLLECTION
		COLUMNS
			ANOTHER_ID VARCHAR2(32)  PATH ”ANOTHER_ID”,
			ID_STATUS VARCHAR2(32)   PATH ”ID_STATUS”
		GROUP group_2
		XMLTABLE MY_METADATA ”/a”
		COLUMNS
			data VARCHAR2(32) 		PATH ”c/data”,
			link VARCHAR2(32) 		PATH ”d/link”,
			purpose VARCHAR2(32) 	PATH ”d/purpose”,
			urgency VARCHAR2(32) 	PATH ”c/urgency”,
			case_no VARCHAR2(32) 	PATH ”s/case_no”,
			num_1 VARCHAR2(32) 		PATH ”c/normalised/num_1”,
			num_2 VARCHAR2(32) 		PATH ”c/normalised/num2”
END;
/

CREATE
INDEX MY_IDX ON TEST_TABLE
(
	“XML_DOC”
)
INDEXTYPE IS “XDB”.”XMLINDEX”
LOCAL
PARAMETERS
(‘PARAM indexParam’);

The interesting here is that I couldn’t get the above code to work initially when following the example in the Oracle documentation for REGISTERPARAMETER:

DBMS_XMLINDEX.REGISTERPARAMETER (
   ‘myIndexParam’,
   ‘PATH TABLE po_ptab
	PATH ID INDEX po_pidx
	ORDER KEY INDEX po_oidx
	VALUE INDEX po_vidx
	PATHS(NAMESPACE MAPPING(xmlns:p=”http://www.example.com/IPO”))
	GROUP MASTERGROUP XMLTABLE PO_TAB
	(”/p:PurchaseOrder”
		COLUMNS
			REFERENCE VARCHAR2(30) PATH ”p:Reference”,
			REQUESTOR VARCHAR2(30) PATH ”p:Requestor” )
	GROUP ITEMGROUP XMLTABLE ITEMGROUP_TAB
	(”/p:PurchaseOrder/p:LineItems/p:LineItem”
		COLUMNS
			LINENUMBER NUMBER(38) PATH ”@p:ItemNumber”,
			QUANTITY NUMBER(38) PATH ”@p:Quantity”,
			DESCRIPTION VARCHAR2(256) PATH ”p:Description” ));

as it kept giving me an incorrectly coded routine error.

After some trial and error, it turns out that I had to remove the brackets that enclose the text that come after the GROUP item_name XMLTABLE xmltable_name, as observed in the example I provided above. I am not sure if it is a typo in the documentation or something that has to do with an incorrect number of quotation marks because of the string etc, but it was an interesting discovery.