{"id":557,"date":"2023-06-22T12:08:52","date_gmt":"2023-06-22T10:08:52","guid":{"rendered":"https:\/\/anjo.pt\/keyword-oracle\/?p=557"},"modified":"2023-08-26T17:56:30","modified_gmt":"2023-08-26T15:56:30","slug":"how-to-workaround-oracle-text-primary-key-limitations-and-drg-10528","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2023\/06\/22\/how-to-workaround-oracle-text-primary-key-limitations-and-drg-10528\/","title":{"rendered":"How to workaround Oracle Text primary key limitations (and DRG-10528)"},"content":{"rendered":"\n<p>One of my clients had a quite easy desire to try Oracle Text for an existing application. Oracle Text allows to use &#8220;standard SQL to index, search, and analyze text and documents stored in the Oracle database, in files, and on the web&#8221;. <\/p>\n\n\n\n<p>It seemed simple, until we tried to implement on table named after the city where I studied: T_COIMBRA<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\ncreate table T_COIMBRA (COL_ID timestamp not null, COL_TXT varchar2(100));\n\ncreate unique index COIMBRA_IDX_ID on T_COIMBRA(COL_ID);\n\nalter table T_COIMBRA add constraint PK_COIMBRA primary key (COL_ID) using index COIMBRA_IDX_ID;\n\ncreate index COIMBRA_IDX_TXT ON T_COIMBRA(COL_TXT) indextype is ctxsys.context;\n\n<\/pre><\/div>\n\n\n<p>Nothing special it seems. But we get an error:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [6]; title: ; notranslate\" title=\"\">\ncreate index COIMBRA_IDX_TXT ON T_COIMBRA(COL_TXT) indextype is ctxsys.context\n\nError report -\nORA-29855: error occurred in the execution of ODCIINDEXCREATE routine\nORA-20000: Oracle Text error:\nDRG-10528: primary keys of type TIMESTAMP(6) are not allowed\nORA-06512: at &quot;CTXSYS.DRUE&quot;, line 186\nORA-06512: at &quot;CTXSYS.TEXTINDEXMETHODS&quot;, line 320\n29855. 00000 -  &quot;error occurred in the execution of ODCIINDEXCREATE routine&quot;\n*Cause:    Failed to successfully execute the ODCIIndexCreate routine.\n*Action:   Check to see if the routine has been coded correctly.\n<\/pre><\/div>\n\n\n<p>Below I show how to workaround this and keep an &#8220;unsupported&#8221; column as unique identifier of the table.<\/p>\n\n\n\n<!--more Read More-->\n\n\n\n<p>Digging well in the Application Developers Guide documentation for Oracle Text (there is nothing in Oracle Support for the DRG-10528), we find this:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2023\/06\/image-2.png\"><img loading=\"lazy\" decoding=\"async\" width=\"504\" height=\"82\" src=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2023\/06\/image-2.png\" alt=\"Screenshot from Oracle documentation stating which primary key types are supported. Timestamp datatype is not part of the list.\" class=\"wp-image-561\" srcset=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2023\/06\/image-2.png 504w, https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2023\/06\/image-2-300x49.png 300w\" sizes=\"auto, (max-width: 504px) 100vw, 504px\" \/><\/a><\/figure>\n\n\n\n<p>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? <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [7]; title: ; notranslate\" title=\"\">\nSQL&gt; select index_name, index_type, uniqueness,status, domidx_status, domidx_opstatus\n  2    from all_indexes\n  3*  where index_name=&#039;COIMBRA_IDX_TXT&#039;;\n\n        INDEX_NAME    INDEX_TYPE    UNIQUENESS    STATUS    DOMIDX_STATUS    DOMIDX_OPSTATUS\n__________________ _____________ _____________ _________ ________________ __________________\nCOIMBRA_IDX_TXT    DOMAIN        NONUNIQUE     VALID     VALID            FAILED\n<\/pre><\/div>\n\n\n<p>This strange status means that a working application, just stops working (on my case was a Goldengate replication which just &#x1f4a3;, means, it got abended):<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; insert into T_COIMBRA values (systimestamp, &#039;PK has a restricted datatype, it fails&#039;);\n\nError starting at line : 1 in command -\ninsert into T_COIMBRA values (systimestamp, &#039;PK has a restricted datatype, it fails&#039;);\nError at Command Line : 1 Column : 13\nError report -\nSQL Error: ORA-29861: domain index is marked LOADING\/FAILED\/UNUSABLE\n29861. 00000 -  &quot;domain index is marked LOADING\/FAILED\/UNUSABLE&quot;\n*Cause:    An attempt has been made to access a domain index that is\n           being built or is marked failed by an unsuccessful DDL\n           or is marked unusable by a DDL operation.\n*Action:   Wait if the specified index is marked LOADING\n           Drop the specified index if it is marked FAILED\n           Drop or rebuild the specified index if it is marked UNUSABLE.\n\nSQL&gt; drop index COIMBRA_IDX_TXT;\n\nIndex COIMBRA_IDX_TXT dropped.\n<\/pre><\/div>\n\n\n<p>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?<\/p>\n\n\n\n<p>My customer DBA come with a nice idea: just get the primary key constraint away&#8230; and replace it with an unique constraint, using the existing index.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; alter table T_COIMBRA drop constraint PK_COIMBRA keep index;\n\nTable T_COIMBRA altered.\n\nSQL&gt; alter table T_COIMBRA add constraint UK_COIMBRA unique (COL_ID) using index COIMBRA_IDX_ID;\n\nTable T_COIMBRA altered.\n<\/pre><\/div>\n\n\n<p>Will it work, will it not? My expectation was wrong:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; create index COIMBRA_IDX_TXT ON T_COIMBRA(COL_TXT) indextype is ctxsys.context;\n\nIndex COIMBRA_IDX_TXT created.\n\nSQL&gt; select index_name, index_type, uniqueness,status, domidx_status, domidx_opstatus\n  2    from all_indexes\n  3*  where index_name=&#039;COIMBRA_IDX_TXT&#039;;\n\n        INDEX_NAME    INDEX_TYPE    UNIQUENESS    STATUS    DOMIDX_STATUS    DOMIDX_OPSTATUS\n__________________ _____________ _____________ _________ ________________ __________________\nCOIMBRA_IDX_TXT    DOMAIN        NONUNIQUE     VALID     VALID            VALID\n\nSQL&gt; insert into T_COIMBRA values (systimestamp, &#039;If there is only Unique constraint, it works!&#039;);\n\n1 row inserted.\n<\/pre><\/div>\n\n\n<p>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. <\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>One of my clients had a quite easy desire to try Oracle Text for an existing application. Oracle Text allows to use &#8220;standard SQL to index, search, and analyze text and documents stored in the Oracle database, in files, and on the web&#8221;. It seemed simple, until we tried to implement on table named after [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,6,39],"tags":[],"class_list":{"0":"post-557","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-bug","7":"category-oracle","8":"category-oracle-19c","9":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/557","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=557"}],"version-history":[{"count":5,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/557\/revisions"}],"predecessor-version":[{"id":581,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/557\/revisions\/581"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=557"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=557"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=557"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}