How to workaround Oracle Text primary key limitations (and DRG-10528)
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.
Read More