Skip to Main Content
  • Questions
  • Options for returning hierarchical data to node.js

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, David.

Asked: January 12, 2020 - 5:33 pm UTC

Answered by: Connor McDonald - Last updated: January 14, 2020 - 4:58 am UTC

Category: PL/SQL - Version: Oracle 12.2c

Viewed 100+ times

You Asked

First of all I would like to thank you for your support of Node.js and its integration with Oracle through the node-oracledb package. It has been a big part of our success in transitioning from a ui implemented in plsql to one implemented in Vue.js with data access through node.js. We are always trying to find more ways to take advantage of the great features in Oracle and this includes pipelined table functions and nested collections.

Up until this point, when we had to send hierarchical data to the Vue ui, we have accomplished this by calling a new PLSQL procedure which returns multiple separate ref-cursors and using these cursors to reconstruct the hierarchical in node.js before returning it to the client. Now that the node-oracledb module supports returning nested table collections I would like to understand what the pros and/or cons would be to using this new feature in place of separate ref-cursors.

In addition, in order to send hierarchical data into PLSQL we have been sending the information in as a blob of JSON which we then parse and introspect into it using the low level JSON API in PLSQL. This seems more straight forward than splitting the data into separate flat data sets before calling PLSQL. So I would like to you input on the benefits/drawbacks to possibly using nested tables as an option to sending data into PLSQL instead of JSON.

Thanks for any insights/recommendations you can provide.

and we said...

I had a chat with three of our Node specialists - Dan McGhan, Chris Jones and Anthony Tuininga

Here are their comments, pretty much verbatim.

Now that the node-oracledb module supports returning nested table collections I would like to understand what the pros and/or cons would be to using this new feature in place of separate ref-cursors.


I think you are referring to the newer object and collection support: https://oracle.github.io/node-oracledb/doc/api.html#objects

When generating nested JSON, I suppose you have several options:
1. Individual cursors that are stitched together in the mid-tier (what they are doing now).
2. Object/collection support
3. Nested cursor expression support
4. JSON in the database

Of all of these, I think #1 is what you seem to be doing now, would be the toughest in terms of performance and complexity.

For #2, the new feature you're asking about, I see this as being a little rigid still, as you have to create and maintain schema objects that match what you want in the mid-tier. I see this as more advantageous when you need to work with an object that you didn’t create, such as MDSYS.SDO_GEOMETRY.

I would think you would benefit most from #3 (coming around the 20c timeframe) and #4. What you could do with #4 will get better with every database version - I love our support for JSON and wrote about it here:

https://jsao.io/2018/10/relational-to-json-with-sql/

With this type of query, you wouldn’t need to maintain database objects. You would get back JSON strings (varchar2(4000) by default with CLOB support in 18c+). If you didn’t need to do any mid-tier processing, they could skip parsing the JSON and just stream it out to the client from Node.js. If you fetch the CLOBs as Strings, the perf should be pretty good.

So I would like to you input on the benefits/drawbacks to possibly using nested tables as an option to sending data into PLSQL instead of JSON.

Again, I’m not a fan of having to maintain schema objects for this, but maybe some people may like it.

I like what you are doing already, just send the JSON into the database, parse it and do what needs to be done. This keeps the data related logic in the mid-tier as light-weight as possible.

In summary, the potential benefits/drawbacks to using object/collection support are:

Benefits
o Could be nice to have Node.js database code based on objects in the database that are the same shape as those in the mid-tier.

Drawbacks
o Would have to keep the objects in the database in sync with the mid-tier (could complicate schema migrations).
o Potential performance issues.

A good place to get more info and continue the discussion is on the node forum

https://community.oracle.com/community/groundbreakers/database/developer-tools/node_js