Forum Stats

  • 3,770,086 Users
  • 2,253,063 Discussions
  • 7,875,305 Comments

Discussions

Reading long JSON in MLE

Yuri Kirilin
Yuri Kirilin Member Posts: 1 Green Ribbon

How to read and transfer columns of type JSON from Oracle 21c to MLE (Javascript)?

By trial-and-error method, I found a workaround:


let sql = "SELECT JSON_SERIALIZE(M.JSON_DATA RETURNING BLOB) AS JSON_DATA FROM CALC C WHERE C.ID = :calcId";   

let   result = oracledb.defaultConnection().execute(

   sql,

   [calcId],

   { fetchInfo: {"JSON_DATA": {type: oracledb.UINT8ARRAY}} });


const str = String.fromCharCode.apply(null, result.rows[0][0]); // convert Uint8Array to string

const jsonData = JSON.parse(str); // convert string to json object


But it seems to work only if the length of original JSON_DATA is less than 32K. Otherwise, the exception is raised "ORA-24826: value LOB no longer available"

Another workaround would be to convert JSON_DATA to BLOB in advance and to store it in the new column.

What is the correct/native way to read json types? Where can I find more information on magic constants like oracledb.UINT8ARRAY

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 690 Silver Trophy

    The OracleBlob/OracleClob object is probably incorrectly implemented. It is not working with result of json_XXX( ... returning Xlob).

    Json clob may be converted to a "correct" lob by concatenating ||empty_clob(). For blob i not found a standard functions to convert a blob to "correct" lob. Since JSON.parse() do not accept a byte array, there is no reason to use a BLOB .

    declare
      ctx dbms_mle.context_handle_t;
      src varchar2(32767 byte) := q'[
    var odb=require("mle-js-oracledb");
    var con=odb.defaultConnection();
    var res=con.execute("select json_object(x returning blob) from (select rpad(to_clob('B'),66000-1,'*')||'E' x from dual)");
    for(var r of res.rows) {
      var lob=r[0];
      var str=lob.read(10,1)+"..."+lob.read(10, lob.length()-9);
      console.log(lob.length()+"="+str);
    }
      ]';
    begin
      ctx := dbms_mle.create_context();
      dbms_mle.eval(ctx, 'JAVASCRIPT', src);
      dbms_mle.drop_context(ctx);
    end;
    /
    ORA-04161: Database Error
    ORA-24826: value LOB no longer available
    ORA-06512: at "SYS.DBMS_MLE", line 405
    ORA-06512: at line 15
    
    declare
      ctx dbms_mle.context_handle_t;
      src varchar2(32767 byte) := q'[
    var odb=require("mle-js-oracledb");
    var con=odb.defaultConnection();
    var res=con.execute("with function jb(cl clob) return blob as "
                       +"  j json_object_t; "
                       +"begin "
                       +"  j := json_object_t(); "
                       +"  j.put('x', cl); "
                       +"  return j.to_Blob(); "
                       +"end; "
                      +"select jb(x) from (select rpad(to_clob('B'),66000-1,'*')||'E' x from dual)");
    for(var r of res.rows) {
      var lob=r[0];
      var str=lob.read(10,1)+"..."+lob.read(10, lob.length()-9);
      console.log(lob.length()+"="+str);
    }
      ]';
    begin
      ctx := dbms_mle.create_context();
      dbms_mle.eval(ctx, 'JAVASCRIPT', src);
      dbms_mle.drop_context(ctx);
    end;
    /
    
    66008=123,34,120,34,58,34,66,42,42,42...42,42,42,42,42,42,42,69,34,125