We use PLJSON v08_6 for building our JSON objects in PLSQL. It makes life a lot easier and the code more readable. It also means that we can easily parse JSON objects POST(ed) back via AJAX and also printing them out for response object. So we can’t thank Jonas Krogsboell enough for speeding up our development. However it does have a number of limitations for what we need it to do, such as handling javascript functions embedded within the JSON object, and supporting large objects, i.e. CLOB(s).

The good news is that because it’s open source we can get our hands dirty ourselves and update the code to meet our own requirements. So adding CLOB support is quite easy since JSON_ELEMENT uses ANYDATA to store the actual data. This means that we can easily use CLOB(s) without having to update much code at all which highlights good/efficient/clean design by the author!!

Adding CLOB(s) to your existing JSON object/list is straight foward and requires no code changes, here’s a simple example e.g.

DECLARE
  --
  -- JSON Objects
  --
  v_jsonObj    JSON;
  --
  -- JSON Lists
  --
  v_jsonList    JSON_LIST;
  --
  -- CLOB(s)
  --
  v_json_clob   CLOB  := empty_clob;
BEGIN
  dbms_lob.createtemporary( v_json_clob, false, dbms_lob.SESSION );
  ....
  v_jsonObj := JSON('{ "items": "this would be a very large object"}');
  v_jsonObj.to_clob(v_json_clob,FALSE);
  v_jsonList := JSON_LIST();

  v_jsonList.add_elem(anydata.convertclob(v_json_clob));
  v_jsonObj.put('items',anydata.convertclob(v_json_clob));

  json_printer.pretty_print(v_jsonObj,FALSE);
  json_printer.pretty_print_list(v_jsonList,FALSE);
END;

Then to support the printing out of a CLOB the following changes need to occur with JSON_PRINTER package within the CLOB section

  procedure add_to_clob2(buf_lob in out nocopy clob, buf_str in out nocopy varchar2, str CLOB) as
  begin
    dbms_lob.append(buf_lob, buf_str);
    dbms_lob.writeappend(buf_lob,length(str),str);
    buf_str := '';
  end add_to_clob2;

  procedure ppEA(input json_list, indent number, buf in out nocopy clob, spaces boolean, buf_str in out nocopy varchar2) as
    elem json_element;
    x number; t_num number; t_str varchar2(4000); bool json_bool; obj json; jlist json_list; t_clob CLOB := empty_clob;
    arr json_element_array := input.list_data;
  begin
    for y in 1 .. arr.count loop
      elem := arr(y);
      if(elem is not null) then
      case elem.element_data.gettypename
        when 'SYS.CLOB' then
          x := elem.element_data.getclob(t_clob);
          add_to_clob2(buf, buf_str, t_clob);
        when 'SYS.NUMBER' then

I’ve sent a request to Jonas to add this in to the JSON_PRINTER package so hopefully in a later release we’ll have support similar to this without having to update/hack the source ourselves. In the interim I hope that you find this useful if you have the same requirement.