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:


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_blob blob;
    l_clob clob;

    function clob_to_blob(p_clob clob) return blob is
        l_blob blob;
        o1 integer := 1;
        o2 integer := 1;
        c integer := 0;
        w integer := 0;
        sys.dbms_lob.createtemporary(l_blob, true);
        sys.dbms_lob.converttoblob(l_blob, p_clob, length(p_clob), o1, o2, 0, c, w);
        return l_blob;
    end clob_to_blob;

    l_blob := clob_to_blob(p_clob);
    select JSON_SERIALIZE(l_blob 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:

    l_json     json_object_t := json_object_t();
    l_children json_array_t := json_array_t();
    l_clob     clob;
    l_clob := l_json.to_clob;
    l_clob := utility_pkg.format_json(l_clob);

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
Comparing Timestamps with Time Zone
APEX Map Region vs. Google Map Plugin


  1. Hello,

    Thank you much for the above mentioned example. However, the above example is using static hard coded value in the json_array_t. Would you share the same exact example with database variable?

    What is the correct syntax for appending into array where value is a plsql cursor variable or a database column?

    Thank you

Leave a Reply

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