Pretty Print JSON in PL/SQL

I have been working with some code that uses JSON, sometimes fairly large documents of the stuff, and it’s often necessary to send this to the debug log (e.g. DBMS_OUTPUT) for debugging; however, the builtin functions that convert a JSON object to a string (or clob) return the JSON document in one big long line, like this:

{"surname":"Jones","name":"Bob","age":42,"alive":true,"children":[{"name":"Sally","age":10},{"name":"Terrance","age":8},{"name":"Ulyses","age":6}]}

To show this formatted, I added the following function using JSON_SERIALIZE with the “PRETTY” option, to my utility package:

function format_json (p_clob in clob) return clob is
    l_clob clob;
begin
    select JSON_SERIALIZE(p_clob returning clob PRETTY) into l_clob from dual;
    return l_clob;
end format_json;

Note that my function takes a CLOB, not a JSON object, because sometimes I receive the data already as a CLOB and I don’t want to require conversion to JSON before passing it to my formatting function.

Now, when I call this function:

declare
    l_json     json_object_t := json_object_t();
    l_children json_array_t := json_array_t();
    l_clob     clob;
begin
    l_json.put('surname','Jones');
    l_json.put('name','Bob');
    l_json.put('age',42);
    l_json.put('alive',true);
    l_children.append(json_object_t('{"name":"Sally","age":10}'));
    l_children.append(json_object_t('{"name":"Terrance","age":8}'));
    l_children.append(json_object_t('{"name":"Ulyses","age":6}'));
    l_json.put('children',l_children);
    l_clob := l_json.to_clob;
    l_clob := utility_pkg.format_json(l_clob);
end;

I get the following result:

{
  "surname" : "Jones",
  "name" : "Bob",
  "age" : 42,
  "alive" : true,
  "children" :
  [
    {
      "name" : "Sally",
      "age" : 10
    },
    {
      "name" : "Terrance",
      "age" : 8
    },
    {
      "name" : "Ulyses",
      "age" : 6
    }
  ]
}

EDIT 28/10/2025: the initial version of this article included code that would convert the clob to a blob to pass to the json_serialize function. I don’t recall why I did this, assuming it might have been a workaround for a bug in an early version of 19c I might have encountered when passing a clob directly.
In my testing it seems to work find on current versions of the database so I’ve removed the unnecessary conversion.

Jeffrey Kemp 16 April 2021

JSON / PL/SQL /

About Jeffrey Kemp

Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.