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)
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)
ASKER
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?
Also, isn't xml table approach similar as flat nested table using table and cast functions?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
@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?
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?
ASKER
@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.
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.
ASKER
@sdstuber
If it has to be though PLSQL collections, What do you suggest the approach should be?
If it has to be though PLSQL collections, What do you suggest the approach should be?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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;
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;
>>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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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.
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.
ASKER
@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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.