Have you tried using an SQL collection instead?
(I don't have access to ORDS to test it out at this second)
create type personal_id_nt is table of integer; / select deptno, cast( collect( empno ) as personal_id_nt ) personal_ids from emp group by deptno;
Very nice guess...
But it results in an ORDS error message:
- An unexpected error with the following message occurred: null
I was really hopeful for that implementation since it's easy to use and would have solved my problem. I have tried the documentation but can't find any mention on how to create simple typed arrays.
This makes me wonder: Does ORDS support UDTs?
Also, what do the ORDS logs say? (check tomcat/glassfish/wls logs also)
What DB Version are you on? (188.8.131.52 has the JSON functions built in)
the database version is 184.108.40.206 (Oracle supercluster, upgrades managed by Oracle),
The glassfish error doesn't say anything useful:
(plus many more lines)
I can easily do this with pl/sql and htp.prn, but being able to do this natively is preferred,.
I have read a lot of documentation (and blogs) about working with and querying json in 12c, but not about casting to json in sql (such as xmlforest for xml). I initially tried to find a native way to do this, but couldn't find any.
I have read a lot of documentation (and blogs) about working with and querying json in 12c, but not about casting to json in sql (such as xmlforest for xml).
The next release *might* introduce SQL/JSON publishing functions to generate JSON from relational data.