ORDS, SODA & JSON in the Database

If ORDS is generating the JSON, there is a way to tell ORDS that your column is already JSON...

https://www.thatjeffsmith.com/archive/2017/09/ords-returning-raw-json/

Hi Jeff,

Thanks for your reply.  This is interesting to know.

Unfortunately I've not been using ORDS to make the json I am using apex_json.write.  I'm needing a proc called by a GET request which doesn't seem an option via ords

If I write the clob can I then htp.p out the clob?  is that likely to be bad for performance?  That maybe what I look at tomorrow.

a few more hours wasted and no I cannot seem to use htp.p through ords to send out the correct json doc from a clob.

If you want help with ORDS, you're in the right place

If you want help with apex features, you need to jump over to the apex community

The solution that works for me is to use JSON_OBJECT to create the whole document including the data that was in the json column and serve it up with hpt.p.

I was hoping not to have to get so manual with the json creation - and to be able to follow more closely with what I've done elsewhere but it wasn't too bad and I really have spent way to long on this already.

Maybe Oracle will make this easier for us some year...

ORDS generates the JSON for you, you never described what you were trying to achieve with APEX_JSON

What is it that you want to be easier?

Hi Jeff, Thanks for your interest.

I couldn't see how to get ORDS to make the json I wanted it to when using a procedure to pass a cursor to ORDS.  I really needed to use a procedure.  I tried:

1. selecting a column containing JSON.  - It didn't seem possible to get this to format correctly.
2. Using a cursor within a cursor to create the nested json.  - This isn't possible.
3. I did try using ords based on SQL - I did get the json I wanted (plus stuff I didn't need) but for other reasons couldn't use this approach.

At this point I gave up on getting ORDS to create the json and tried:

1. Using the apex_json package to make the json for me and writing our via htp.p on ords.  (which is very odd maybe I'm missing something here?  I could if I saved the json to a clob , inserted into a table and then selected the json it again!  (I don't know why I had to write the clob to a table.)  This solution was too convoluted/unmaintainable for me)   BTW Using apex_json to convert a cursor to json is working well for me and is my go to approach where there isn't a cursor within a cursor requirement, partly because then I can control the output better - our clients don't expect some of the stuff ORDS adds to the output.  If we put a CDN in front of the API then the links from ORDs seem problematic?  Using apex_json works well for writing json to a table for me even in the cursor within a cursor scenario - its a shame that this only works when writing out a clob not when writing out to http.

Finally I made the json more manually - using JSON_OBJECT, JSON_ARRAYAGG etc and nesting as needed which does seem a little primitive and possibly error prone but it worked - and then I wrote this out using htp.p (breaking the clob down into varchar2 sized chunks)

This got the desired result and seems reasonably performant.  (preparing the json in advance would've had performance advantages but there are be other methods I can use to get the performance desired.)uess

I guess I'm lucky I could fall back to more primitive approaches till I could find one I could make work for me.

Hopefully that's clear, accurate and reasonably logical!

not really

you never actually described your issues with the procedure/cursor and the json formatting

what did you try?

what did you get?

what did you need to have changed for it to be sufficient?

Hi Jeff,

Json formatting issues are the same as the formatting issues in the original post above.  Your method for selecting a json column only worked for me if the source was SQL not for a stored proc.  This wasn't a good option for me.

Having a cursor within a cursor simply didn't work.  I posted a question about it and you answered suggesting it worked in with ORDS based on 'plain ole sql' which I took as confirmation it couldn't be done in proc.  :  cannot execute cursor with nested cursor through ords