Constructing JSON string from Oracle DB

Question

I have a Web application which gets it data from a JSON string. The JSON is in the following format

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

Now this is a sample data. My actual DB is in Oracle. My question would be how do I construct 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.

To summarise:

• Use the DBMS_XMLGEN package to generate XML from a SYS_REFCURSOR.
• Then transform it using this XSLT.

I like it because there's no manual generation and because you have the option of returning XML too by skipping the final transformation.