14.2.4.4 Serializing JSON in PL/SQL

Serialize PL/SQL JSON objects to text with STRINGIFY or TO_CLOB, depending on size.

For a variable of type JSON_OBJECT_T, two object functions let you "serialize" its data to textual format:

• STRINGIFY – when the text does not exceed 32767 characters, or
• TO_CLOB – for a JSON object of any size.

The following example containing a JSON object with a property having a value of length 32767 shows how TO_CLOB works correctly. However, uncommenting the call to STRINGIFY produces an error since the JSON object's text representation exceeds 32767 characters.

declare
    l_value varchar2(32767) := lpad('y',32767,'x');
    l_json  json_object_t   := json_object_t();
    -------------------------------------------------------
    procedure p(p_clob in clob)
    is
        l_offset integer := 1;
        l_chunk  varchar2(32767);
    begin
        while l_offset <= dbms_lob.getlength(p_clob) loop
        l_chunk := dbms_lob.substr(p_clob, 32767, l_offset);
        dbms_output.put_line(l_chunk);
        l_offset := l_offset + 32767;
        end loop;
    end p;  
begin
  l_json.put('name',l_value);
  -- Gives ORA-40478: output value too large
  -- p(l_json.stringify);
  p(l_json.to_clob);
end;

Tip:

When calling object functions on JSON types, ensure the variable is not null. Calling an object function on a null produces the error message:

ORA-30625: method dispatch on NULL SELF argument is disallowed