?
Solved

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

Posted on 2014-07-29
14
Medium Priority
?
270 Views
Last Modified: 2014-09-29
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
Comment
Question by:loginboy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
14 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40227973
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
 

Author Comment

by:loginboy
ID: 40228053
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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 40228061
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 74

Expert Comment

by:sdstuber
ID: 40228136
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
 

Author Comment

by:loginboy
ID: 40228303
@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
 

Author Comment

by:loginboy
ID: 40228307
@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
 

Author Comment

by:loginboy
ID: 40228992
@sdstuber

If it has to be though PLSQL collections, What do you suggest the approach should be?
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 40229036
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
 

Author Comment

by:loginboy
ID: 40229382
@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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40229394
>>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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 40229486
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
 

Author Comment

by:loginboy
ID: 40230894
@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
 

Author Comment

by:loginboy
ID: 40230896
@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
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 40232841
Post sample inputs and expected outputs and we can help with the code.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question