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.

Media Resource – treat the response as binary – do NOT do any of the magic-JSON-ification stuff.

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!
thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

12 Comments

  1. Hello,

    What changes in the case of a post handler? I mean in case I want to return from a stored procedure a json output via a clob, how should my post handler be defined ?
    thanks in advance,

    Anisa

  2. Hi Jeff,
    What if your are using PL/SQL for your JSON response, the media resource is not available in that type. I am facing the same problem. and I am getting the JSON response escaped, converting the JSON to a JSON.

    Regards,
    Omar

  3. Hello Jeff, isn’t it possible to return the contents of a table/query as a xml, instead of json/csv?

    Thank you.

  4. Andrew McPherson Reply

    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.

    • Andrew McPherson

      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.

    • thatjeffsmith

      If you have a stored procedure, why can’t you code the query specific to the value of CONTRACT_TYPE?

    • Andrew McPherson

      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.

    • thatjeffsmith

      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.

Write A Comment