Building a JSON String from an Oracle DB Database

Question

I have a web application that receives data from a JSON string. JSON is in the following format

{ "contacts": [{ "type": "contact", "name": "John Doe", "contact": 1, "links": ["Spouse", "Friends","Jane Doe","Harry Smith"] }] } 

Now this is sample data. My actual DB is in Oracle. My question will be how can I build this JSON from Oracle.

Answer 1

This is the best method I've come across: http://ora-00001.blogspot.sk/2010/02/ref-cursor-to-json.html .

Summarizing:

• Use the DBMS_XMLGEN package to generate XML from SYS_REFCURSOR .
• Then convert it using XSLT .

I like it because there is no manual generation and because you have the option to return the XML by skipping the final conversion.