Hi,
(TDM 3.4.15.10, Oracle 10g r2)
I have a table with a schema based XMLType column, which I create an index on, for example;
CREATE TABLE data_table(
…
data_xml XMLType,
…
)
TABLESPACE TBSP
XMLType “data_xml” STORE AS CLOB
XMLSCHEMA “data.xsd”
ELEMENT “XXXX”;
CREATE INDEX data_num_idx
ON data_table (
SUBSTR( EXTRACTVALUE (data_xml, ‘/XXXX//Number’),1,4),
SUBSTR( EXTRACTVALUE (data_xml, '/XXXX//Series’),1,1),
SUBSTR( EXTRACTVALUE (data_xml, ‘/XXXX/*/Year’),1,4),
SUBSTR( EXTRACTVALUE (data_xml, ‘//DataId’),1,4)
);
After RE the index script is generated as;
CREATE INDEX DATA_NUM_IDX ON DATA_TABLE
(SUBSTR(EXTRACTVALUE(SYS_MAKEXML(‘8C23A8A6D38831CEE092909853804B18’,4815,“SYS_NC00003$”),’/XXXX//Number’),1,4),
SUBSTR(EXTRACTVALUE(SYS_MAKEXML(‘8C23A8A6D38831CEE092909853804B18’,4815,“SYS_NC00003$”),’/XXXX//Series’),1,1),
SUBSTR(EXTRACTVALUE(SYS_MAKEXML(‘8C23A8A6D38831CEE092909853804B18’,4815,“SYS_NC00003$”),’/XXXX/*/Year’),1,4),
SUBSTR(EXTRACTVALUE(SYS_MAKEXML(‘8C23A8A6D38831CEE092909853804B18’,4815,“SYS_NC00003$”),’//DataId’),1,4))
LOGGING
TABLESPACE TBSP
NOPARALLEL
/
I think this is a correct representation of the index as Oracle has created it (as shown in TOAD). However it is not transportable. If the schema is destroyed and reloaded (or a different target db is used) the magic numbers change.
RE needs to somehow extract the original SQL text.
Also the field in the index dialog is a single line text field. A Multiline field would make something this long much more understandable.
Regards
Malcolm