The code below is take directly from example file ex11.sql that comes with PL/JSON. All I added was the loop code for making the string larger because I wanted to test a true clob. It fails with certain lengths and I can't figure out why.
32,763 runs
32,764 fails
32,768 runs
Results continue on like this.
NOTE: Once you add the 8 extra hard coded json chars the true lengths for the above examples become: 32,771 (runs), 32,772 (fails), and 32,776 (runs).
Any ideas on how to get this to work consistently?
ex11.sql...
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;
/
Error...
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "CFEADM.JSON_PRINTER", line 10
ORA-06512: at "CFEADM.JSON_PRINTER", line 211
ORA-06512: at "CFEADM.JSON_PRINTER", line 238
ORA-06512: at "CFEADM.JSON_PRINTER", line 256
ORA-06512: at "CFEADM.JSON", line 274
ORA-06512: at line 16
Last edit: Gordon Frobenius 2014-12-22
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
If the string is close enough to the 32767 limit then then combination of escapeString() and llcheck() can cause it to grow beyond that point. escapeString can replace a number of single chars with 2 chars and llcheck() adds 2 chars as well. Thus add_to_clob() bombs because the 3rd param is a varchar2. Thoughts? Is this a bug that can be fixed in a new version?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I will look at it as soon as I can, but I'm very busy at the moment. If you
determine that you've found a bug and have a fix for it, please submit a
pull request on the github page -- http://github.com/pljson/pljson
If the string is close enough to the 32767 limit then then combination of
escapeString() and llcheck() can cause it to grow beyond that point.
escapeString can replace a number of single chars with 2 chars and
llcheck() adds 2 chars as well. Thus add_to_clob() bombs because the 3rd
param is a varchar2. Thoughts? Is this a bug that can be fixed in a new
version?
"All governments suffer a recurring problem: Power attracts pathological
personalities. It is not that power corrupts but that it is magnetic to the
corruptible. Such people have a tendency to become drunk on violence, a
condition to which they are quickly addicted."
(this question along with a screen shot of the error can also be viewed here: http://stackoverflow.com/questions/27608461/oracle-pl-json-to-clob-fails)
The code below is take directly from example file ex11.sql that comes with PL/JSON. All I added was the loop code for making the string larger because I wanted to test a true clob. It fails with certain lengths and I can't figure out why.
Any ideas on how to get this to work consistently?
ex11.sql...
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;
/
Error...
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "CFEADM.JSON_PRINTER", line 10
ORA-06512: at "CFEADM.JSON_PRINTER", line 211
ORA-06512: at "CFEADM.JSON_PRINTER", line 238
ORA-06512: at "CFEADM.JSON_PRINTER", line 256
ORA-06512: at "CFEADM.JSON", line 274
ORA-06512: at line 16
Last edit: Gordon Frobenius 2014-12-22
I may have found the bug, second set of eyes would be great. In json_printer.ppMem there is this line...
add_to_clob(buf, buf_str, llcheck('"'||escapeString(mem.get_string)||'"'));
If the string is close enough to the 32767 limit then then combination of escapeString() and llcheck() can cause it to grow beyond that point. escapeString can replace a number of single chars with 2 chars and llcheck() adds 2 chars as well. Thus add_to_clob() bombs because the 3rd param is a varchar2. Thoughts? Is this a bug that can be fixed in a new version?
I will look at it as soon as I can, but I'm very busy at the moment. If you
determine that you've found a bug and have a fix for it, please submit a
pull request on the github page -- http://github.com/pljson/pljson
On Tuesday, December 23, 2014, Gordon Frobenius gfrobenius@users.sf.net
wrote:
--
James Sumners
http://james.roomfullofmirrors.com/
"All governments suffer a recurring problem: Power attracts pathological
personalities. It is not that power corrupts but that it is magnetic to the
corruptible. Such people have a tendency to become drunk on violence, a
condition to which they are quickly addicted."
Missionaria Protectiva, Text QIV (decto)
CH:D 59