Much of the content you find here is coming directly from people like you – googling for things like ‘JSON_OBJECT_T and ORDS.’

Hi Jeff, what would be the best way when having a function returning JSON_OBJECT_T?


‘Best’ is such a fun word. I would say Paul, that it depends. But let’s look at maybe the most straightforward way, just printing it.

JSON_OBJECT_T is a PL/SQL structure that allows us to work with JSON in PL/SQL (Oracle Docs.)

So let’s assume we’re going to have a GET handler, and we’re doing to have a PL/SQL block of code do the work, presumably generate some JSON using this PL/SQL object type of JSON_OBJECT_T.

The code

  jc_raw clob;
  jc_pretty clob;
  keys_string VARCHAR2(100);
  jo := JSON_OBJECT_T.parse('{"name":"fred",
                              "projects":["json", "xml"]}');
  jc_raw := jo.TO_CLOB;
  SELECT JSON_SERIALIZE(jc_raw returning clob PRETTY) INTO jc_pretty FROM dual;
  owa_util.mime_header( 'application/json', TRUE, 'UTF-8' );

Let’s call it now

Build up your object, convert it to a CLOB, print it.

Is this ‘the best?’

It’s perhaps the most straightforward. Put your JSON into a clob or blob, then print it with HTP.P. I’m being nice and using OWA to set the mime type in the response header…so the call to JSON_SERIALIZE with the PRETTY option isn’t necessary if your browser has a JSON extension to pretty format the response for you.

I’m just a fan of the JSON functions in general. Beda and his crew have built a really nice solution for both Oracle developers that dig relational but also want the flexibility of JSON AND for those developers that want to do everything in JSON but have the power, security, and features of the Oracle Database.

Hint: Read Beda’s blog to see what’s possible with JSON in the Oracle Database.

And of course he’s on Twitter as well.


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


  1. Avatar

    Thanks Jeff, just wanted to mention that the code has two DECLARE…

Write A Comment