How can I use methods on Packages which return JSON on oracledb

98371a90-2be6-40fe-a096-6c83d8f354e4

Hello...

I started exploring oracledb package in order to possibly substitute ORDS. Every request we have published declares a JSON variable that gets populated when we run a function from a PL/SQL Package. Is it possible to run that function in oracledb that returns a JSON data type from Oracle?

danmcghan-Oracle

Hello 98371...

What do you mean by JSON data type? What version of the database are you using?

I'm guessing you're either using PL/JSON or built-in JSON types in 12.2. In either case, you should be able to serialize the JSON object to a string (or CLOB). The driver has supported CLOBs as streams for a while but recent enhancements make it even easier to fetch CLOBs as strings without having to stream them.

Regards,

Dan

Christopher Jones-Oracle

If you're not using Oracle Database's JSON support you are probably already storing your JSON as VARCHAR or CLOB.  As Dan mentioned, you can easily transfer JavaScript strings in Node.js to and from these Oracle types using node-oracledb.

The Oracle Database JSON datatype support was introduced is in 12.1.0.2 (with enhancements in 12.2).  It actually also stores the data in VARCHARs or LOBs, so node-oracledb can access the JSON directly with normal inserts & updates.  The extra database-side JSON features make working with data much easier, e.g. you can access individual entries in nested JSON objects by using x.y.z syntax.   Check out Database JSON Developer's Guide - Contents  Also see the node-oracledb documentation and examples at https://github.com/oracle/node-oracledb/blob/master/doc/api.md#jsondatatype  and https://github.com/oracle/node-oracledb/tree/master/examples

(see selectjson.js and selectjsonclob.js)