My Oracle Support Banner

ORA-40467 from JSON_TEXTCONTAINS on an upgraded 12.1.0.2 database (Doc ID 1956727.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.

Symptoms

In a 12.1.0.2 database that was upgraded from 11.2.0.x, a JSON_TEXTCONTAINS query fails with error:

SQL> show user
USER is "JSONTEST"
SQL> SELECT po_document
  2  FROM j_purchaseorder
  3  WHERE json_textcontains(po_document, '$.LineItems.Part.Description', 'Magic');
SELECT po_document
       *
ERROR at line 1:
ORA-40467: JSON_TEXTCONTAINS() cannot be evaluated without JavaScript Object Notation (JSON) index

The error occurs despite the existence of a VALID JSON Text search index on the table and column being searched:

SQL> connect / as sysdba
Connected.
SQL> COLUMN column_name format a15
SQL> SELECT i.owner, i.table_name, i.index_name, c.idx_text_name column_name, i.status,i.domidx_status,i.domidx_opstatus
     FROM ctxsys.ctx_indexes c, dba_indexes i
     WHERE i.owner = 'JSONTEST'
       AND c.idx_owner = i.owner
       AND c.idx_name = i.index_name;

OWNER      TABLE_NAME      INDEX_NAME           COLUMN_NAME     STATUS   DOMIDX_STATU DOMIDX
---------- --------------- -------------------- --------------- -------- ------------ ------
JSONTEST   J_PURCHASEORDER PO_DOCUMENT_INDEX    PO_DOCUMENT     VALID    VALID        VALID


Querying CTXSYS.DR$OBJECT_ATTRIBUTE shows the OAT_DATATYPE has the value of "I" for JSON_ENABLE and BSON_ENABLE:

SQL> show user
USER is "SYS"
SQL> COL oat_name FORMAT A20
SQL> SELECT oat_id, oat_datatype, oat_name, oat_cla_id, oat_att_id
     FROM ctxsys.dr$object_attribute
     WHERE oat_system = 'N'
      AND oat_name in ('JSON_ENABLE','BSON_ENABLE');

    OAT_ID O OAT_NAME             OAT_CLA_ID OAT_ATT_ID
---------- - -------------------- ---------- ----------
     50817 I JSON_ENABLE                   5         17
     50819 I BSON_ENABLE                   5         19


Querying CTXSYS.DR$SECTION_GROUP_ATTRIBUTE may also show a "t" or "T" as SGA_VALUE for JSON_SECTION_GROUP and BSON_SECTION_GROUP instead of a "1" :

SQL> show user
USER is "SYS"
SQL> COL sga_value FORMAT A10
SQL> SELECT sga_value, sga_sgat_id, sgp_name
     FROM ctxsys.dr$section_group_attribute, ctxsys.dr$section_group
     WHERE sga_sgat_id IN (50817, 50819)
       AND dr$section_group_attribute.sga_id = dr$section_group.sgp_id;

SGA_VALUE  SGA_SGAT_ID SGP_NAME
---------- ----------- ------------------------------
T                50817 JSON_SECTION_GROUP
T                50819 BSON_SECTION_GROUP

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.