Oracle: create index on json, Json item lenght is too long long?

Question

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

Answer 1

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> 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> truncate table tj;

Table truncated.

SQL> 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> 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> truncate table tj;

Table truncated.

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

Answer 2

Without any loop any other package just concatenate the clob strings

with data as   ( select 
    xmlelement(e,regexp_replace('{"name":"'||colname||'"}', '[[:cntrl:]]', ''),',') col1
    from tblname   )   select
        rtrim(replace(replace(replace(xmlagg(col1).getclobval(),'&'||'quot;','"'),'<E>',''),'</E>',''),',')
        as very_long_json   from data;