Some time ago I noticed this blog from Dan McGhan. In that blog he compares several ways to generate JSON from relational data in a Oracle Database.
I had some spare time, so I tried my own JSON generator, build around 3 nested Oracle types, on the examples he used.
I had no problem generating the exact output he wanted, but the performance was a bit disappointing (almost the same as the PL/JSON solution).
But, as I had some spare time, I started trying to improve the performance. But after a few tries it turned out that I was lucky to get a result at all.
My JSON generator had a gigantic memory leak. I not sure if it’s because of Oracle’s implementation of the nested types I used
create or replace type jd authid current_user as object ( json_type varchar2(1 char) ) not final; / create or replace type ja as table of jd; / create or replace type jv authid current_user under jd ( .... )
but using this script is enough to crash my 12.1.02.0 database
declare
x jv;
begin
for i in 1 .. 12
loop
x := jv( 'a', jv( 'b', jv( 'x' ) )
, 'a', jv( 'b', jv( 'x' ) )
);
end loop;
end;
/
Anyway, I had some spare time and turned my JSON generator in a one package implementation. And using that package I can create the same JSON as Dan is creating with PL/JSON and APEX_JSON.
But with performance a bit better than the APEX_JSON solution.
declare
--
function get_dept_as_json( p_dept_id number )
return clob
is
cursor c_dept
is
select d.department_id
, d.department_name
, l.location_id
, l.street_address
, l.postal_code
, c.country_id
, c.country_name
, c.region_id
, m.employee_id
, m.first_name || ' ' || m.last_name manager_name
, m.salary
, j.job_id
, j.job_title
, j.min_salary
, j.max_salary
from departments d
, locations l
, countries c
, employees m
, jobs j
where d.department_id = p_dept_id
and l.location_id = d.location_id
and c.country_id = l.country_id
and m.employee_id (+) = d.manager_id
and j.job_id (+) = m.job_id;
r_dept c_dept%rowtype;
l_jv as_json.tp_json_value;
l_emps as_json.tp_json_value;
l_hist as_json.tp_json_value;
l_date_format varchar2(20) := 'DD-MON-YYYY';
l_rv clob;
begin
open c_dept;
fetch c_dept into r_dept;
close c_dept;
--
dbms_lob.createtemporary( l_rv, true, dbms_lob.call );
l_jv := as_json.json( 'id', as_json.jv( r_dept.department_id )
, 'name', as_json.jv( r_dept.department_name )
, 'location', as_json.json( 'id', as_json.jv( r_dept.location_id )
, 'streetAddress', as_json.jv( r_dept.street_address )
, 'postalCode', as_json.jv( r_dept.postal_code )
, 'country', as_json.json( 'id', as_json.jv( r_dept.country_id )
, 'name', as_json.jv( r_dept.country_name )
, 'regionId', as_json.jv( r_dept.region_id )
)
)
, 'manager', as_json.json( 'id', as_json.jv( r_dept.employee_id )
, 'name', as_json.jv( r_dept.manager_name )
, 'salary', as_json.jv( r_dept.salary )
, 'job', as_json.json( 'id', as_json.jv( r_dept.job_id )
, 'title', as_json.jv( r_dept.job_title )
, 'minSalary', as_json.jv( r_dept.min_salary )
, 'maxSalary', as_json.jv( r_dept.max_salary )
)
)
);
--
for r_emp in ( select e.employee_id
, e.first_name || ' ' || e.last_name name
, e.hire_date
, e.commission_pct
from employees e
where e.department_id = r_dept.department_id
)
loop
l_hist := null;
for r_hist in ( select h.job_id
, h.department_id
, h.start_date
, h.end_date
from job_history h
where h.employee_id = r_emp.employee_id
)
loop
l_hist := as_json.add_item( l_hist
, as_json.json( 'id', as_json.jv( r_hist.job_id )
, 'departmentId', as_json.jv( r_hist.department_id )
, 'startDate', as_json.jv( r_hist.start_date, l_date_format )
, 'endDate', as_json.jv( r_hist.end_date, l_date_format )
)
);
end loop;
l_emps := as_json.add_item( l_emps
, as_json.json( 'id', as_json.jv( r_emp.employee_id )
, 'name', as_json.jv( r_emp.name )
, 'isSenior', as_json.jv( r_emp.hire_date < to_date( '01-jan-2005', 'dd-mon-yyyy' ) )
, 'commissionPct', as_json.jv( r_emp.commission_pct )
, 'jobHistory', l_hist
)
);
end loop;
as_json.add_member( l_jv, 'employees', l_emps );
--
l_rv := as_json.stringify( l_jv );
as_json.free;
return l_rv;
end;
begin
dbms_output.put_line( get_dept_as_json( 10 ) );
end;
Anton
![Sorting PL/SQL Collections, the quite simple way (part two: Have the SQL Engine do the heavy lifting)]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "Sorting PL/SQL Collections, the quite simple way (part two: Have the SQL Engine do the heavy lifting)")
Hi Anton,
Thanks for the package.
For me it is very useful as the apex_json package is giving me some problems on a 12.1 database and the jsontable functionality is not yet available on 12.1 for creating json.
However I ran into a small issue.
When I use de json_ec function it raises a too many open cursors error at line 1521.
This is happening because the rc ref cursor isn’t closed in the dynamic sql code.
If you add ” close rc;” after the loop statement at line 1530, it is solved.
regards,
Mathieu
Hello Anton,
Thanks very much for this package.
It is very useful when you have a database version older than 12.2.
As I ran into problems with the apex_json package
There is a bug I ran into when I used the json_ec function:
“ORA-01000: maximum open cursors exceeded at line 1524″.
The code will execute some fetching of a ref cursor using execute immediate.
But the ref cursor isn’t closed afterwards.
So add the line ” close rc;” after the loop at line 1530 and it is solved.
regards,
Mathieu
Hi I’m very happy with this resource as_json but I cant update or delete the value of existing member :(.
Someone knows how do it??
Hi Anton,
thank you for the package. I’m finding it invaluable. However, when I have a number less than 1, that is already represented as JSON, such as the ‘Pitch’ value in the JSON below:
{“Item #”:”T9070360″,”d1″:”#4″,”Pitch”: 0.66,”Limit”:”Z3″,”l1″:50,”l2″:17,”l3″:null,”d2″:3.55,”sq”:2.8,”l4″:5,”z”:3,”Drill Ø”:3}
when I add it to a larger object as a member (using:
as_json.add_member(l_jp,’items’, AS_JSON.json(l_ritem)); //the l_ritem contains the item metadata object above
)
, as part of a larger string, then the number loses it’s leading 0:
“itemMetadata”:{“Item #”:”T9070360″,”d1″:”#4″,”Pitch”:.66,”Limit”:”Z3″,”l1″:50,”l2″:17,”l3″:null,”d2″:3.55,”sq”:2.8,”l4″:5,”z”:3,”Drill \u00D8″:3}
I originally wrote a simple function to create the correct JSON number identifier and I had to modify my to_char to fix it on the original string. I hoped that would be the issue. However I can’t find the equivalent in the as_json package as I think it will be a similar problem?
Thanks for your help,
Regards,
Steve
And then I noticed the leading space… I guess the clue was in the line break added in the post above. However as_json still has the same problem, even when the leading space is removed for +ve decimals < 1
Thanks
I fixed the issue, see version 12, https://technology.amis.nl/wp-content/uploads/2015/12/as_json12.txt
Thanks Anton,
It now works as expected.
Best Regards,
Steve
Hi Anton
Thank you for your package AS_JSON which offers valuable insights. I am struggling with a similar task since a couple of days, but my select statements to create the json data contain sub-cursors embedded with the cursor function, as in
select d.*
,cursor (
select e.*
from emp e
where e.deptno = d.deptno
) employee
from dept d ;
Such columns can be described by dbms_sql.describe_columns3, they yield a col_type of 102. I didn’t find a branch processing this very col_type in AS_JSON, but I must confess that I don’t know of any way manipulating columns with type cursor with dbms_sql, as well. According to Tom Kyte, there actually is no way (or was no way in 20123).
On the other hand, DBMS_XMLQUERY, DBMS_XMLGEN and APEX_JSON somehow do the magic. DBMS_XMLQUERY maybee uses Java, but I am pretty sure DBMS_XMLGEN and APEX_JSON don’t. Can you imagine how this can be done ?
Regards
Pascal
According to the documentation apex_json uses xmltype to do that magic, https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_json.htm#CHDHHHHH
as_json has a parser for xmltype, so you can use that same trick, as_json.json( xmltype( your_cursor ) )
I will publish a new version of as_json this week. That version will have code to parse a refcursor with embedded cursor.
Thank you for the hint to the documentation.
I wasn’t aware of the xmltype constructor recieving a refcursor before.
And of course, I am looking forward to the new version of as_json. Where will we find it ?
In have updated the links in this blog, https://technology.amis.nl/wp-content/uploads/2015/12/as_json11.txt