Hello Team,
I've run into a problem while creating the Index on XMLType column. I was able to create the same index in the previous versions(e.g. 21c).
Index: (Failed to create in 23ai)
CREATE INDEX ARCHIVE_HIST_XML_IX ON ARCHIVEHISTORY (DATA) INDEXTYPE IS XDB.XMLINDEX PARAMETERS ('PATH TABLE HIST_PATH_IX PIKEY INDEX HIST_PIKEY_IX VALUE INDEX HIST_VALUE_IX')
Error:
[ERROR] 2024-11-12 21:21:20,824
ORA-29958: ODCI_FATAL returned by the implementation of the ODCIINDEXCREATE routine for index "ARCHIVE_HIST_XML_IX".
java.sql.SQLException: ORA-29958: ODCI_FATAL returned by the implementation of the ODCIINDEXCREATE routine for index "ARCHIVE_HIST_XML_IX".
ORA-30956: invalid option for XML index
Table:
-- ARCHIVEHISTORY definition
-- DDL generated by DBeaver
-- WARNING: It may differ from actual native database DDL
CREATE TABLE ARCHIVEHISTORY (
ID VARCHAR2(35) NOT NULL,
EVENTTYPE NUMBER(4,0) NULL,
RECORDDUP NUMBER(1,0) NULL,
EVENTRECORDID NUMBER(38,0) NULL,
TASKID NUMBER(38,0) DEFAULT 0 NULL,
EVENTDEFID VARCHAR2(256) NULL,
"ACTION" NUMBER(5,0) NULL,
SENDTO VARCHAR2(254) NULL,
SENDTOFULLNAME VARCHAR2(256) NULL,
SENDTOTYPE NUMBER(10,0) NULL,
RECFROM VARCHAR2(254) NULL,
RECFROMFULLNAME VARCHAR2(256) NULL,
RECFROMTYPE NUMBER(10,0) NULL,
ORIGINALDEST VARCHAR2(254) NULL,
ORIGINALDESTFULLNAME VARCHAR2(256) NULL,
ORIGINALDESTTYPE NUMBER(10,0) NULL,
ORIGINATOR VARCHAR2(256) NULL,
ORIGINATORFULLNAME VARCHAR2(256) NULL,
ORIGINATORTYPE NUMBER(10,0) NULL,
SENDER VARCHAR2(254) NULL,
SENDERFULLNAME VARCHAR2(256) NULL,
DATAMODIFIEDBY VARCHAR2(256) NULL,
RETURNTOWQID NUMBER(10,0) NULL,
RETURNTOWQNAME VARCHAR2(50) NULL,
ACTIONTIME DATE DEFAULT SYSDATE NULL,
DURATION NUMBER(38,0) NULL,
FORMREV VARCHAR2(60) NULL,
SUBJECT VARCHAR2(256) NULL,
TRANSACTIONLEVEL NUMBER(7,0) NULL,
GENERALINFO1 VARCHAR2(256) NULL,
GENERALINFO2 VARCHAR2(256) NULL,
NOTE CLOB NULL,
"DATA" SYS.XMLTYPE(2000) NULL,
CONSTRAINT ARCHIVE_ID_INDX PRIMARY KEY (ID),
CONSTRAINT SYS_C009906 CHECK ("ID" IS NOT NULL),
CONSTRAINT SYS_C009907 CHECK ("EVENTTYPE" IS NOT NULL),
CONSTRAINT SYS_C009908 CHECK ("EVENTRECORDID" IS NOT NULL),
CONSTRAINT SYS_C009909 CHECK ("TASKID" IS NOT NULL),
CONSTRAINT SYS_C009910 CHECK ("ACTIONTIME" IS NOT NULL)
);
CREATE INDEX ARCHIVE_ACTIONTIME_INDEX ON ARCHIVEHISTORY (ACTIONTIME);
CREATE INDEX ARCHIVE_EVENTTYPE_INDX ON ARCHIVEHISTORY (EVENTTYPE);
CREATE INDEX ARCHIVE_FORMREV_INDEX ON ARCHIVEHISTORY (FORMREV);
CREATE UNIQUE INDEX ARCHIVE_ID_INDX ON ARCHIVEHISTORY (ID);
CREATE INDEX ARCHIVE_RECORDID_TASKID_INDX ON ARCHIVEHISTORY (EVENTRECORDID,TASKID);
CREATE INDEX ARCHIVE_SENDER_INDX ON ARCHIVEHISTORY (SENDER);
any idea how to fix?