One of my clients had a quite easy desire to try Oracle Text for an existing application. Oracle Text allows to use “standard SQL to index, search, and analyze text and documents stored in the Oracle database, in files, and on the web”.
It seemed simple, until we tried to implement on table named after the city where I studied: T_COIMBRA
create table T_COIMBRA (COL_ID timestamp not null, COL_TXT varchar2(100));
create unique index COIMBRA_IDX_ID on T_COIMBRA(COL_ID);
alter table T_COIMBRA add constraint PK_COIMBRA primary key (COL_ID) using index COIMBRA_IDX_ID;
create index COIMBRA_IDX_TXT ON T_COIMBRA(COL_TXT) indextype is ctxsys.context;
Nothing special it seems. But we get an error:
create index COIMBRA_IDX_TXT ON T_COIMBRA(COL_TXT) indextype is ctxsys.context
Error report -
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10528: primary keys of type TIMESTAMP(6) are not allowed
ORA-06512: at "CTXSYS.DRUE", line 186
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 320
29855. 00000 - "error occurred in the execution of ODCIINDEXCREATE routine"
*Cause: Failed to successfully execute the ODCIIndexCreate routine.
*Action: Check to see if the routine has been coded correctly.
Below I show how to workaround this and keep an “unsupported” column as unique identifier of the table.
Digging well in the Application Developers Guide documentation for Oracle Text (there is nothing in Oracle Support for the DRG-10528), we find this:
However, what is really not funny at all, is that the index remains there! And the domain index is a partly in an invalid state. Why, Oracle?
SQL> select index_name, index_type, uniqueness,status, domidx_status, domidx_opstatus
2 from all_indexes
3* where index_name='COIMBRA_IDX_TXT';
INDEX_NAME INDEX_TYPE UNIQUENESS STATUS DOMIDX_STATUS DOMIDX_OPSTATUS
__________________ _____________ _____________ _________ ________________ __________________
COIMBRA_IDX_TXT DOMAIN NONUNIQUE VALID VALID FAILED
This strange status means that a working application, just stops working (on my case was a Goldengate replication which just 💣, means, it got abended):
SQL> insert into T_COIMBRA values (systimestamp, 'PK has a restricted datatype, it fails');
Error starting at line : 1 in command -
insert into T_COIMBRA values (systimestamp, 'PK has a restricted datatype, it fails');
Error at Command Line : 1 Column : 13
Error report -
SQL Error: ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE
29861. 00000 - "domain index is marked LOADING/FAILED/UNUSABLE"
*Cause: An attempt has been made to access a domain index that is
being built or is marked failed by an unsuccessful DDL
or is marked unusable by a DDL operation.
*Action: Wait if the specified index is marked LOADING
Drop the specified index if it is marked FAILED
Drop or rebuild the specified index if it is marked UNUSABLE.
SQL> drop index COIMBRA_IDX_TXT;
Index COIMBRA_IDX_TXT dropped.
The quick solution is of course to drop the index, which I immediately did. But the question remained: what would be the best way to use Oracle Text on an existing application, without strongly change the table structure?
My customer DBA come with a nice idea: just get the primary key constraint away… and replace it with an unique constraint, using the existing index.
SQL> alter table T_COIMBRA drop constraint PK_COIMBRA keep index;
Table T_COIMBRA altered.
SQL> alter table T_COIMBRA add constraint UK_COIMBRA unique (COL_ID) using index COIMBRA_IDX_ID;
Table T_COIMBRA altered.
Will it work, will it not? My expectation was wrong:
SQL> create index COIMBRA_IDX_TXT ON T_COIMBRA(COL_TXT) indextype is ctxsys.context;
Index COIMBRA_IDX_TXT created.
SQL> select index_name, index_type, uniqueness,status, domidx_status, domidx_opstatus
2 from all_indexes
3* where index_name='COIMBRA_IDX_TXT';
INDEX_NAME INDEX_TYPE UNIQUENESS STATUS DOMIDX_STATUS DOMIDX_OPSTATUS
__________________ _____________ _____________ _________ ________________ __________________
COIMBRA_IDX_TXT DOMAIN NONUNIQUE VALID VALID VALID
SQL> insert into T_COIMBRA values (systimestamp, 'If there is only Unique constraint, it works!');
1 row inserted.
What a heck! So it just checks for a primary key and it fails. If the constraint is a unique key in one of the unsupported data types, then it works.