Solved

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

Posted on 2014-07-29
14
260 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
  • 7
  • 4
  • 3
14 Comments
 
LVL 76

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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 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
 
LVL 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 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 76

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 73

Assisted Solution

by:sdstuber
sdstuber earned 250 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 76

Accepted Solution

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

760 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now