Previously I had an AJAX PL/SQL callback that returned a relatively simple JSON string, using the LISTAGG technique described by Lucas here.
declare
l_json varchar2(32767);
begin
select listagg('{"id":"'||version||'","content":"'||version||'.x"}',',') within group (order by null) json
into l_json
from (
select distinct substr(version,1,1) version
from apex_timeline
order by version
);
sys.htp.prn('['||l_json||']');
end getGroups;
Now it can be further simplified by taking LISTAGG out of the equation, which can be a problem for larger data sets:DECLARE
c sys_refcursor;
BEGIN
open c for
select version as id
,version||'.x' as content
from (
select distinct substr(version,1,1) version
from apex_timeline
order by version
);
apex_json.write(c);
END;
The output differs only slightly[{"id":"1","content":"1.x"},{"id":"2","content":"2.x"},{"id":"3","content":"3.x"},{"id":"4","content":"4.x"},{"id":"5","content":"5.x"}]
[ { "ID":"1" ,"CONTENT":"1.x" } ,{ "ID":"2" ,"CONTENT":"2.x" } ,{ "ID":"3" ,"CONTENT":"3.x" } ,{ "ID":"4" ,"CONTENT":"4.x" } ,{ "ID":"5" ,"CONTENT":"5.x" } ]A few extra spaces in the apex_json version. If lower case required for JSON attributes then use double quotes around column aliases, ie:
select version as "id"Oracle 12c JSON APIs seem to be all about validating and deconstruction, while this API has a bunch of overloaded
apex_json.write() modules, including support for CLOBs.Craig Sykes demonstrated how this could be done dynamically with a simple page.
