Skip to Main Content

Reading long JSON in MLE

Yuri KirilinNov 4 2021

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

Comments
Post Details
Added on Nov 4 2021
7 comments
94 views