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.

With || operator. Just leave alone formatting JSON and concentrate on feeding data. If you post your data structure (the tables you want this data to generate from), and show us what have you already tried, we may tell you how to actually do it. — David Jashi
– David Jashi, Commented Jul 9, 2013 at 6:06
This might be helful code.google.com/p/plsql-utils and look for title Generate JSON using PL/SQL and Parse JSON using PL/SQL — Jacob
– Jacob, Commented Jul 9, 2013 at 6:56
@Polppan, You got it right... I do need a PL/SQL procedure. does anybody know how i can contruc this? — AnaMaria
– AnaMaria, Commented Jul 9, 2013 at 9:55

manadart · Accepted Answer · 2015-07-22 15:22:25Z

6

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.

edited Jul 22, 2015 at 15:22

answered Jul 23, 2013 at 7:47

manadart

1,82012 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MattD Over a year ago

Please include the relevant items from your link in your answer, as well as explanations for them. Should that site ever change or be deleted your accepted answer becomes useless to future viewers.

Adamantish Over a year ago

This may be the only way but it has to be said that it's not a very sustainable way. JSON is a far more light-weight markup than XML. This answer is basically saying that nobody has made a solution public for programatically presenting oracle data in the world's favourite API format.

manadart Over a year ago

That is factually incorrect. The answer is what provides a public, programmatical solution for presenting Oracle data in the worlds favourite API format. If you have a better answer post it and get it voted on. Down-voting for no better reason than not liking the particular solution defies sense.

Collectives™ on Stack Overflow

Constructing JSON string from Oracle DB

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related