Oracle PL / JSON to_clob () Fails

The code below is taken directly from the example file ex11.sql

that comes with PL / JSON. All I added was code for looping to make the line larger, because I wanted to test a real clob. It fails with a specific length and I can't figure out why.

  • 32,763 in operation
  • 32,764 does not work
  • 32,768 runs
  • The results continue as follows.
  • NOTE. After adding 8 extra hardcoded json characters, the true lengths for the above examples would be: 32.771 (works), 32.772 (fails), and 32.776 (works).

Any ideas on how to get this to work consistently?

set serveroutput on;

declare
  teststringlength pls_integer := 32763;
  i pls_integer := 0;
  obj json;
  my_clob clob := '{"a":"';

begin

  while i < teststringlength loop
    my_clob := concat(my_clob,'X');
    i := i + 1;
  end loop;
  my_clob := concat(my_clob,'"}');

  obj := json(my_clob);
  obj.print;
  dbms_lob.trim(my_clob, 0); --empty the lob
  obj.to_clob(my_clob);
  dbms_output.put_line('----');
  dbms_output.put_line(my_clob);
  --example with temperary clob
  my_clob := empty_clob();
  dbms_lob.createtemporary(my_clob, true);
  obj.to_clob(my_clob, true);
  dbms_output.put_line('----');
  dbms_output.put_line(my_clob);
  dbms_lob.freetemporary(my_clob);

end;
/

      

enter image description here

+3


source to share





All Articles