JSON Search Index - Part VI (Handling ORA-29855 / DRG-51401 (Duplicate SEARCH_ON))

 

In the earlier posts of this series, we explored how the SEARCH_ON clause controls the behavior of a JSON search index, whether it is optimized for full-text queries using TEXT, value-based predicates using VALUE, or a combination of both using TEXT_VALUE. In this post, let us look at a slightly tricky error that can arise when these options are combined incorrectly.

 

Consider the following index definition:

 

create search index my_demo_idx on my_demo(c2)
for json parameters(' search_on
VALUE(number) include ($.products.id)
TEXT include ($.products.str)
TEXT_VALUE(number) include ($.products.quantity) ');

 

At first glance, this looks perfectly valid. The intention is quite clear: index products.id for numeric comparisons, index products.str for text search, and allow products.quantity to support both text and numeric queries. However, this statement fails with the error:

 

demo@ADB26AI> create search index my_demo_idx on my_demo(c2)
  2  for json parameters(' search_on
  3     VALUE(number) include ($.products.id)
  4     TEXT include ($.products.str)
  5     TEXT_VALUE(number) include ($.products.quantity) ');
create search index my_demo_idx on my_demo(c2)
*
ERROR at line 1:
ORA-29855: Error while processing the ODCIINDEXCREATE routine for index "DEMO"."MY_DEMO_IDX".
ORA-20000: Oracle Text error:
DRG-51401: duplicate SEARCH_ON specification for TEXT component
ORA-06512: at "CTXSYS.DRUE", line 192
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 359
Help: https://docs.oracle.com/error-help/db/ora-29855/

 

To understand why this happens, we need to look a little deeper into how Oracle interprets the SEARCH_ON clause. The important detail here is that TEXT_VALUE is not an entirely separate mode. Internally, it already includes both text and value indexing capabilities. This means that whenever you use TEXT_VALUE, you are implicitly asking Oracle to create a TEXT component as well.

 

Now, if you also specify a standalone TEXT clause in the same definition, Oracle sees this as defining the same component twice. In other words, the index definition is attempting to assign two different rules to the same TEXT behavior. Rather than making assumptions about which one should take precedence, Oracle raises the DRG-51401 error to prevent ambiguity.

 

This restriction is intentional. Allowing overlapping definitions like this would make it unclear how different JSON paths should be indexed and queried. It could lead to inconsistencies in how text search is executed, how tokens are generated, or how scoring behaves. By enforcing that each SEARCH_ON component is defined only once, Oracle ensures that the index definition remains unambiguous and predictable.

 

The solution, therefore, is to make the indexing intent explicit and avoid overlapping specifications. Instead of mixing TEXT and TEXT_VALUE, we can define the required behavior using TEXT and VALUE directly, grouping the relevant paths under each.

 

A corrected version of the index definition would look like this:

 

demo@ADB26AI> create search index my_demo_idx on my_demo(c2)
  2  for json parameters(' search_on
  3     VALUE(number) include ($.products.id, $.products.quantity)
  4     TEXT include ($.products.str, $.products.quantity)');
 
Index created.

 

In this version, the intent is much clearer. The paths that need numeric or range-based evaluation are grouped under VALUE(number), while the paths that should participate in full-text search are grouped under TEXT. If a path such as products.quantity needs to support both types of queries, it is explicitly included in both sections. This avoids any hidden overlap and keeps the definition straightforward.

 

The key takeaway from this is that TEXT_VALUE should not be used alongside TEXT in the same index definition, since it already includes text indexing internally. When both text and value semantics are required, it is better to express them explicitly using separate TEXT and VALUE clauses. This not only resolves the error but also results in a cleaner and more maintainable index design.

 

As with the earlier topics in this series, the broader lesson here is to think in terms of query patterns rather than just syntax. A well-defined JSON search index should clearly reflect how the data will be queried. When that intent is expressed without overlap or ambiguity, both errors and performance surprises can be avoided.