Forum Stats

  • 3,782,057 Users
  • 2,254,587 Discussions
  • 7,879,904 Comments

Discussions

how to name xmltype column index

Andy Steel
Andy Steel Member Posts: 79
edited Nov 10, 2008 6:42AM in SQL & PL/SQL
In Oracle 11g, the following command creates a table with a single XMLTYPE column.

CREATE TABLE EMPLOYEE
(
XMLTYPE details
);

Oracle will create an index called something like SYS_IL0000211081C00005$$ automatically for the column without me doing anything else. I presume this is because it's stored as a LOB behind the scenes.

Does anyone know how I can specify the name of the index when the column is created as opposed to leaving it to Oracle ? I need to control my index names for assisting schema comparisons.

Thanks in advance
Andy
Tagged:

Answers

  • 605982
    605982 Member Posts: 13
    ALTER INDEX eSYS_IL0000211081C00005$$
    RENAME TO <Newname>
  • Andy Steel
    Andy Steel Member Posts: 79
    Thanks, but it's not really answering my problem. I would first have to identify the column using PL/SQL then perform the DDL to rename it.

    I need to be able to specify the index name when creating the table column.
  • 605982
    605982 Member Posts: 13
    [http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_2.shtml] - DDL Commands for LOBs


    Actually, one can not alter index on LOB column once created...

    Above link provide info on creating LOB columns and related indexes, and (hopefully) solution for your question.
    605982
  • Andy Steel
    Andy Steel Member Posts: 79
    Thanks, the following bit in bold shows it working.

    CREATE TABLE CPNRULACTPRM
    (
    RULACTPRMIDD NUMBER, /* PK */
    RULACTIDD1 NUMBER, /* Rule Action - FK:CPNRULACT->RULACTIDD */
    PRMIDD1 NUMBER, /* Action Parameter Name - FK:CPNRULPRMDEF.PRMIDD */
    RULACTPRMXML XMLTYPE, /* Action Parameter Value. XML conforming to FormattedExpression.xsd */
    RULACTPRMORD NUMBER, /* Order (cosmetic only) */
    CONSTRAINT IDX_PK_CPNRULACTPRM_PRMIDD
    PRIMARY KEY (RULACTPRMIDD) USING INDEX TABLESPACE INDEXES,
    CONSTRAINT CPNRULACTPRM_RULACTIDD1_NN
    CHECK (RULACTIDD1 IS NOT NULL),
    CONSTRAINT CPNRULACTPRM_PRMIDD1_NN
    CHECK (PRMIDD1 IS NOT NULL),
    CONSTRAINT CPNRULACTPRM_RULACTPRMORD_NN
    CHECK (RULACTPRMORD IS NOT NULL),
    CONSTRAINT CPNRULACTPRM_RULACTPRMXML_NN
    CHECK (RULACTPRMXML IS NOT NULL)
    ) TABLESPACE CONFIG
    XMLTYPE RULACTPRMXML
    STORE AS CLOB (
    TABLESPACE CONFIG
    INDEX IDX_RULACTPRMXML_LOB (
    TABLESPACE INDEXES
    STORAGE (MAXEXTENTS UNLIMITED)
    *)*
    *)*;
This discussion has been closed.