ORDS: Returning Raw {JSON}

thatjeffsmith ORDS 6 Comments

Tell Others About This Story:

ORDS is nice.

It auto-formats your SQL or PL/SQL results and response to JSON before returning to your client or application.

But what if you’re response is ALREADY in JSON?

Like this:

Oracle can be your JSON DB, seriously.

Bad

ORDS trying to convert JSON to JSON

Yuck.

Good

You telling ORDS we’re not just dealing with a CLOB, it’s actually JSON.

We’ve talked about the MEDIA stuff before.

Here’s that media post…
The Media resource in this case is application/json – and we’re going to let the browser handle that, just like it would a BLOB and a PDF. We just need to include the heads-up notice saying what it actually is, so adjust your GET query text to include ‘application/json’ before you include the JSON itself.

Ta-da!

Tell Others About This Story:

Comments 6

  1. Hi,
    The problem with this method is the inability to use ORDS to generate URI’s. REST API conventions call for URI’s to identify objects that can be retrieved, and this only appears to be an ORDS facility if it generates the JSON itself.
    I would really like to see ORDS support a hybrid mechanism that allowed JSON elements to be returned as columns from the query, so that URI generation and other ORDS features could be mixed with both 12c calls, and PL/SQL functions that returned complex objects.
    For instance we have a system that has an entity CONTRACTS, however, it is heavily sub typed (more than 20, with two levels of sub types). I can generate all the optional components from PL/SQL easily enough, but there is no way to incorporate into the query, so it has to do a total replacement, and that causes an immediate issue for URI generation, forcing me to do them statically, not truly dynamically, which gives problems with testing.

    1. thatjeffsmith Post
      Author
      1. If I use a SELECT I can have paths modified by ORDS. As a simple case, start with my Contract header:
        SELECT id “$uri”, ‘../customers/’||cust_id “$customer, contract_type, name, start_date, …
        FROM contracts
        WHERE id = :id;
        Gives my links and the common contract details.
        However, there are variable details depending on contract_type. But there is no way to provide a variable number of columns in the query. Being able to return a string with the appropriate JSON pairs for those conditional columns would be a real advantage.
        But its not just conditional columns, its also variations for embedded objects.
        All contracts have lines, so I can add those in with CURSOR(SELECT product, unit_price, percentage FROM contract lines WHERE contract_id = :id), but variance contracts have volume breaks below contract lines, and there is no way to include those conditionally. I would prefer to call a PL/SQL function instead of the CURSOR that returned the JSON object for the lines, so I could handle all the various contract types.
        At the moment what I have is just a PL/SQL procedure that produces the JSON, but building the URI’s was a problem.
        If that doesn’t describe it adequately I can put all the details into a document and email it to you.

        1. thatjeffsmith Post
          Author
          1. Hi Jeff,
            Clearly I am not explaining myself well.
            I like ORDS. I find the ease of constructing queries to retrieve data a major feature.
            When we started our REST API we focused on the GET handlers to flesh out our thoughts and had about 30 built very quickly.
            That’s how we discovered the difficulties with deep structures, and sub types.
            For ones like contracts we have had to write them entirely in PL/SQL, when I would have preferred to just create individual pieces in PL/SQL to populate the complex parts.
            Once we went to PL/SQL for the entire output, we had the problem of correctly generating URI’s to embed, something that is done for us by ORDS in a query. That was somewhat difficult, and required understanding that the components can be retrieved with OWA_UTIL.
            That’s really why I say it would be an advantage to be able to retrieve JSON components as column data, both as simple pairs to optionally include columns, and as an embedded JSON object.
            You already use $ as the first character in a column name to identify columns that need path processing, you could continue that with say # to identify a column that already contains JSON.
            My goal would be to minimize the use of PL/SQL, but if that is not a direction you want to go, then providing support to PL/SQL to access the same facilities would be second best.

          2. thatjeffsmith Post
            Author

            Ok, thanks for sticking with me, I understand now 🙂

            I think you have an excellent idea for an ER, where we could pull out the $ from an OUT param and know to apply the URI magic to it just like we do for SQL.

Leave a Reply

Your email address will not be published. Required fields are marked *