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
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
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
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
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 |