Oracle: create index on json, Json item lenght is too long long? I trying to create a index a clob json field. Is anyone know what could be the limit. I have up to 200,000 char. length json record.

Thanks for any tips. 

CREATE INDEX emul_full_idx ON CDP_emultimedia (JsonData)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('section group CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)')
SQL&gt; /
CREATE INDEX emul_full_idx ON CDP_emultimedia (JsonData)

ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50850: ORA-06515: PL/SQL: unhandled exception JSON item length is too long
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366
I could not find any official reference to this, but by experimenting, I found that a JSON record value that is longer than 32K triggers this error.

Maybe this will be fixed in a future release, like this similar bug, that is said to be fixed in 12.2 (this bug is about having JSON identifies longer than 64 bytes, not this error):

Creating a JSON Index Fails with DRG-50850 (Doc ID 2110588.1)

Until then, I would try to use shorter records, or open a Service Request.

And the experiment:

CREATE TABLE tj  (js CLOB  CONSTRAINT c_js CHECK (js IS JSON));


I can easily create an index on "large" (over 200K character) JSON documents, so first a JSON with a lot (50000) of short (2 characters) records:

declare
  c clob;
begin
  c := '{';

  for i in 1..50000
  loop
    c := c || to_clob('"Attr') || to_clob(i) || to_clob('" : "') || lpad(to_clob('X'), 2, 'X') || to_clob('",');
  end loop;

  c := c || '"End" : 0 }';

  insert into tj values (c);
end;
/


The length of this single JSON is:

select dbms_lob.getlength(js) from tj;

DBMS_LOB.GETLENGTH(JS)
----------------------
                938906


Creating the index:

SQL&gt; CREATE INDEX tj_i1 ON tj (js)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('section group CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');

Index created.


Now try a JSON with 1 long record:

SQL&gt; truncate table tj;

Table truncated.

SQL&gt; drop index tj_i1;

Index dropped.

declare
  c clob;
begin
  c := '{';

  for i in 1..1
  loop
    c := c || to_clob('"Attr') || to_clob(i) || to_clob('" : "') || lpad(to_clob('X'), 32800, 'X') || to_clob('",');
  end loop;

  c := c || '"End" : 0 }';

  insert into tj values (c);
end;
/


So our JSON has 1 long record, the overall length is:

select dbms_lob.getlength(js) from tj;

DBMS_LOB.GETLENGTH(JS)
----------------------
                 32825


Now creating the index:

SQL&gt; CREATE INDEX tj_i1 ON tj (js)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('section group CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');

CREATE INDEX tj_i1 ON tj (js)
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50850: ORA-06515: PL/SQL: unhandled exception JSON item length is too long
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366


The same works when below the 32K limit:

SQL&gt; truncate table tj;

Table truncated.

SQL&gt; drop index tj_i1;

Index dropped.

declare
  c clob;
begin
  c := '{';

  for i in 1..1
  loop
    c := c || to_clob('"Attr') || to_clob(i) || to_clob('" : "') || lpad(to_clob('X'), 32700, 'X') || to_clob('",');
  end loop;

  c := c || '"End" : 0 }';

  insert into tj values (c);
end;
/


Length, and index:

select dbms_lob.getlength(js) from tj;

DBMS_LOB.GETLENGTH(JS)
----------------------
                 32725

CREATE INDEX tj_i1 ON tj (js)
INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS ('section group CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');

Index created.