pljson_util_pkg sql_to_json doesn't work if values are less than 1

Question

I have a problem with pljson_util_pkg.sql_to_json

declare 
  -- Local variables here
  tstjson_list pljson_list;
  l_Result_json_clob clob;
begin
  -- Test statements here
  tstjson_list := pljson_list();
  dbms_lob.createtemporary(l_Result_json_clob, true);

  tstjson_list := pljson_util_pkg.sql_to_json('SELECT 0.1 as tmp from dual');
  tstjson_list.to_clob(l_Result_json_clob);
end;

When I execute this code I am getting error message: Scanner problem with the following input: {"ROWSET":{"ROW":{"TMP":.1}}}

It looks like that if value is less than 1, then this error occurs because instead of 0.1 the result is .1! Any idea why?

Thank you, Zoran

Answer 1

Already closed, sorry

I think the reason is how oracle handle numbers in xml...

If you try this:

select xmlelement("tmp",0.1) from dual

you're going to receive the same result...

So, in order to achieve your gol just format the number via to_char function:

select xmlelement("tmp",to_char(0.1,'FM0.00')) from dual

Bye Nicola