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_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; begin 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; begin 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:
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 } ] }
darsh
19 August 2023 - 4:03 am
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