CODEWITHSUNDEEP
jsonoracle-database
AnaMaria
AnaMaria

Reputation: 3621

Constructing JSON string from Oracle DB

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.

Upvotes: 6

Views: 25624

Answers (1)

manadart
manadart

Reputation: 1810

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.

Upvotes: 6

Related Questions