My Oracle Support Banner

Oracle Text: Invalid Data In Dr$preference_value Causes ORA-01722: Invalid Number (Doc ID 2615284.1)

Last updated on APRIL 27, 2020

Applies to:

Oracle Text - Version 12.2.0.1 and later
Information in this document applies to any platform.

Symptoms

On : 18.5.0.0.0 version, Creating/Maintaining context indexes

Oracle Text: invalid data in dr$preference_value causes ORA-01722: invalid number

Several 12.2 databases and also an upgrade 18c database has this defect.
A new database created using 18.3 does not have this defect.

select * from ctxsys.ctx_preference_values;
ERROR:
ORA-01722: invalid number

Cause: invalid data in dr$preference_value
These two rows cause the problem:
CTXSYS CTXSYS.JSONREST_GERMAN_DIN_LEXER MIXED_CASE YES
CTXSYS CTXSYS.JSONREST_GERMAN_LEXER MIXED_CASE YES

Those are boolean attributes and the value needs to be 0 or 1. 'YES' cannot work with the code of the view ctx_preference_values:
decode(oat_datatype, 'B', decode(prv_value, 1, 'YES', 'NO'), nvl(oal_label, prv_value)) prv_value

You can use this query:

select /*+ ORDERED INDEX(dr$preference_value) */
  u.name prv_owner
 ,pre_name prv_preference
 ,oat_name prv_attribute
 --,decode(oat_datatype, 'B', decode(prv_value, 1, 'YES', 'NO'), nvl(oal_label, prv_value)) prv_value
 ,prv_value
 --,decode(prv_value, 1, 'YES', 'NO')
from
  sys."_BASE_USER" u
 ,dr$preference
 ,dr$preference_value
 ,dr$object_attribute
 ,dr$object_attribute_lov
where prv_value = nvl(oal_value, prv_value)
  and oat_id = oal_oat_id (+)
  and oat_id = prv_oat_id
  and prv_pre_id = pre_id
  and pre_owner# = u.user#
and oat_datatype= 'B'
 

ERROR
-----------------------
ORA-01722: invalid number

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.