Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

n level (dynamic) deep nested collection - input to procedure - Update and Insert on table

Hi Gurus,

Please advice. Need some ideas to kick start this POC. Thanks in advance.

1) Input to the SP a Collection nested to N-levels.
2) Cannot create collection run-time (dynamically)?
3) Should I make a flat structure?  (Which collection is best)?
4) How to I ensure that reading from the array is in order? (Which Looping method)?
5) This n-level deep nested collection needs to be updated/inserted into 3 table (2 of them are self-referenced - EMP - MGR relation - again, N levels)
0
loginboy
Asked:
loginboy
  • 7
  • 4
  • 3
4 Solutions
 
slightwv (䄆 Netminder) Commented:
Go XML.  It has a hierarchy and relationships built in.

Pass the XML into the procedure as a CLOB then you can either use the DOM (not really necessary these days) or a query using XMLTABLE to return the values just like selecting from a table.
0
 
loginboyAuthor Commented:
Nice, i'll try a poc with xmltable. Proc is invoked by .net, and they have json and not xml, xmltable still work?

Also, isn't xml table approach similar as flat nested table using table and cast functions?
0
 
slightwv (䄆 Netminder) Commented:
JSON is different from XML so, no.  XMLTABLE works with XML.

If the proc is called from .Net, XML should be pretty simple.  You might look for a JSON to XML converter that will take care of things for you.

I've seen a few questions here on this site where people have tried to use PL/SQL to parse JSON but it gets pretty complex.  Since you have nested objects, it would likely get even worse if you tried to write your own.

There are a few JSON parsers in PL/SQL that you might look into.  I cannot recommend any since I've never used any.

For example:
http://sourceforge.net/projects/pljson/

>>Also, isn't xml table approach similar as flat nested table using table and cast functions?

Maybe but it is written and maintained as part of the database engine.  Why write a lot of custom code when you don't need to?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sdstuberCommented:
If you have oracle 12.1.0.2 then you have json parsing natively.

What are your n-levels?  Are they collections of the same type or collections of different types?  If different, are they truly dynamic or is there some finite (and probably small) set of choices for each level.

Collections can nest.
0
 
loginboyAuthor Commented:
@slightwv
1) Ok, so no XMLTABLE
2) JSON to XML either in .Net or PLSQL doesn't seem feasible.
3)  if it has to be though PLSQL collections
What do you suggest the approach should be?
0
 
loginboyAuthor Commented:
@sdstuber
We are in oracle 11g.
Collections is of 4 fields (parent) 5th field is child again same fields and parent... And this can go on upto n-levels.
0
 
loginboyAuthor Commented:
@sdstuber

If it has to be though PLSQL collections, What do you suggest the approach should be?
0
 
sdstuberCommented:
Something like this?


CREATE OR REPLACE TYPE person;

CREATE OR REPLACE TYPE person_tab AS TABLE OF REF person;

CREATE OR REPLACE TYPE person AS OBJECT
(
    id NUMBER,
    first_name VARCHAR2(100),
    last_name VARCHAR2(100),
    birth_date DATE,
    children person_tab
);
0
 
loginboyAuthor Commented:
@sdstuber

1) I like the idea (I've modified it slightly see at the end). So technically, the n-level nested JSON can be populated in this n-level nested table. SP loop can be done in order (?)  and to insert or update, so that Parent gets created (if not already exists) before the children gets processed. And Loop until children count = 0. I believe the whole thing be in 1 external loop or something. I'll give it a try.

2) Do you suggest this approach over creating 1 flat (no nested, no children) nested table.
And the invoker of the SP is responsible to populate this array in the order the JSON is as SP don't have visibility.
This way SP can just rely on the order of the data.

CREATE OR REPLACE TYPE emp AS OBJECT
(
    emp_id      NUMBER,
    ename       VARCHAR2(100),
    job_title   VARCHAR2(25),
    MGR_ID      NUMBER,
 STATIC FUNCTION initialize RETURN emp);
 /
 
 101 ABC CEO NULL
 102 XYZ CIO 101
 103 PQR DIRECTOR 102

------------------------
Initialization:

CREATE OR REPLACE TYPE person AS OBJECT
(
    id NUMBER,
    first_name VARCHAR2(100),
    last_name VARCHAR2(100),
    birth_date DATE,
    children person_tab,
 STATIC FUNCTION initialize RETURN person);
 /

CREATE OR REPLACE TYPE BODY person AS
 STATIC FUNCTION initialize RETURN person IS
 BEGIN
    RETURN (person(null,
                   null,    
                   null,
                   null,
                   null));
 END initialize;
END;
/

Rollback, - DROP TYPE person_tab FORCE; DROP TYPE person FORCE;
0
 
slightwv (䄆 Netminder) Commented:
>>And the invoker of the SP is responsible to populate this array

If the invoker has to parse the JSON to populate a user defined data type, why can they not transform the JSON into XML and make things much less complex?
0
 
sdstuberCommented:
Can you create a small but complete example of what you want to do?
That is, post a sample json along with the 3 tables and the data you would expect to see in those tables from the source json.

I'm confident the nested collections will work, but it will take effort to populate them and then you have to iterate through the collection to produce the end result.

In other words - no intermediate representation has any value in itself.  Whether a flat table, an xmltype or a nested collection - they are all simply vehicles between the json and your final 3 tables.  Is that correct?

If so then I'd lean toward a procedure that accepted the json AS IS.  So, a varchar2 or clob (if  size > 32K) and then your procedure would parse the json and write to the tables.

Then, if/when you upgrade to 12.1.0.2 you can take advantage of the native json parsing inside your procedure to make it more efficient but with no external api changes.
0
 
loginboyAuthor Commented:
@sdstuber
1) Sure, I'm working on example.
2) Will try the nested collection approach.
3) You are correct, they are just vehicles.But for static nested collections we have already used UDTs, iterations, error validations and upsert the whole array into the table(s).
4) I thought you previously suggested, PL-JSON can get complicated if  nested collection esp if they are dynamic. And i don't see we upgrading anytime sooner. Also I think I'd try XML input rather than JSON.
0
 
loginboyAuthor Commented:
@slightwv
They'll convert, lot of tools out there. I tried  a poc on XML input, progress is slow. I don't have hands on PLSQL with XML.
0
 
slightwv (䄆 Netminder) Commented:
Post sample inputs and expected outputs and we can help with the code.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now