6 Comments

  1. tze hon
    6/6/2011

    Hi,

    I am unable to alter my local XMLIndex to add a structured component and I was hoping you could help shed some light on the problem. The local XMLIndex was previously created successfuly on a range partitioned relational table with an XMLType column. The range partition is on a normal date column.

    -- On 11g Release 2
    -- Creating unstructured index.
    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');
    -- Error: Object name has to be specified for DML statements involving system
    -- generated partitions

    As a side note, I have previously created all the unstructured and structured components for my XMLIndex on a non partitioned table successfully, so I was wondering if there is some problem with my syntax since I am now trying it on a partitioned table.

    I apologise for the lack of the exact ORA error code due to some issues arising from the fact that my internet pc is separate from my dev pc. I will update the post once I get the error code.

    Thanks in advance for any pointers.

  2. tze hon
    6/7/2011

    Hi,

    The error msg I get when trying to alter my index to add a structured component is

    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 method
    29874. 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.

  3. 6/7/2011

    Thats a good one.

    I guess somewhere we have to fit the LOCAL hint in there, but don’t know how to get this done via DBMS_XMLINDEX.

    I would try again not using the DBMS_XMLINDEX method but after creating the initial unstructured XMLIndex adding the structured XMLIndex via a ALTER INDEX statement while using the LOCAL hint.

  4. tze hon
    6/8/2011

    Hi,

    Thanks for the solution and syntax. The ALTER INDEX (without DBMS_XMLINDEX) method works but it failed when my parameter string is > 1000 characters long as that is the limit for the parameter string. This occurs because I attempt to add multiple groups in one parameter string. Based on your solution, I broke it into multiple ALTER INDEX statements for each group and it works.

    As an alternative to the above solution, I registered my parameter and created the XMLIndex with the parameter in one go, creating both unstructured and structured components together. I cobbled together the following code after looking at the DBMS_XMLINDEX.REGISTERPARAMETER documentation:

    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. 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.

    In any case, thanks for the example, that really helped.

    • 6/15/2011

      I had fun setting up the example, so glad of being of help. I created an enhancement request with Oracle, a while ago, regarding the documentation and hopefully this will get better in the next release.

Comments are closed.