PL JSON clob

  • Sofia Rearden

    Sofia Rearden - 2013-02-20

    Jonas, thanks for the great product! PLJSON is awesome, works well! 
    Please advise, if I can do some adjustments to make conversion from clob to josn a little faster? json_obj:=JSON(json_clob) takes about  6 seconds with an object 440572 char long.

  • Anonymous - 2013-03-21

    We are having a similar problem, but in reverse. We build up a json object that ends up being about 350kb. Then we call htp() on the json object. The call to htp() takes about 10 seconds.

    Please advise. Is there another method, or another way we can do this using json_printer? We don't need any 'pretty printing'. We just need to get the json data to the client quickly.

  • kwaamkit

    kwaamkit - 2013-03-21

    (sorry that last post is from me, but it seems improperly linked to my open id).

    Anyway, also wanted to say thanks for this great library. We'd love to use it as much as possible. It's saving us a lot of time.

    But again, with a 350kb json object, json.htp() is taking approx 10 seconds.

    What are your recommendations for getting better performance? No pretty printing needed, just want to serialize the json.

    Thanks again. Cheers.

  • Jonas Krogsboell

    The htp problem could be fixed by avoiding the to_clob and output directly to htp. The fix should be written in pretty_printer.
    I don't know a solution to the parsing problem - a short demo that illustrates the problem could be useful.


  • kwaamkit

    kwaamkit - 2013-03-23

    Thanks for the response Jonas.

    I just tried that and it actually ends up being slower than the current pretty_printer code (even without all the pretty printing logic). I'm guessing the many small calls to htp.p are expensive. It seems like to best bet is to fill a varchar2 or clob buffer and then dump the data to htp.p periodically.

    Will see what I can do and if it works contribute it to the library. I think others might have use for this too.

  • kwaamkit

    kwaamkit - 2013-03-26

    Recently profiled htp(). It's not the to_clob that's expensive, it's the call the getobject().

    4541205 75.4% 134 0.0% 4541071 75.4% 1 0.0% FTSV2.JSON.HTP (Line 279)
    4539015 75.4% 20 0.0% 4538995 75.4% 1 0.0% FTSV2.JSON_PRINTER.PRETTY_PRINT (Line 248)
    4538849 75.4% 2994 0.0% 4535855 75.4% 1 0.0% FTSV2.JSON_PRINTER.PPOBJ (Line 104)
    4535836 75.4% 107 0.0% 4535729 75.3% 2 0.0% FTSV2.JSON_PRINTER.PPMEM (Line 172)
    4535077 75.3% 217 0.0% 4534860 75.3% 1 0.0% FTSV2.JSON_PRINTER.PPOBJ@1 (Line 104)
    4534838 75.3% 1524 0.0% 4533314 75.3% 1 0.0% FTSV2.JSON_PRINTER.PPMEM@1 (Line 172)
    3624465 60.2% 618705 10.3% 3005760 49.9% 1 0.0% FTSV2.JSON_PRINTER.PPEA (Line 106)
    2885154 47.9% 2885154 47.9% 0 0.0% 872 0.2% SYS.ANYDATA.GETOBJECT (Line 249)

  • kwaamkit

    kwaamkit - 2013-03-26

    should note third column is in microseconds. and the call to getobject comes from the cast (json_value to json_list) in ppea on line 162: ppEA(json_list(elem), indent, buf, spaces, buf_str);

  • Daniel Gagnon

    Daniel Gagnon - 2013-09-26

    How can you make it work for clobs over 32000+ characters ? It doesn't work for me it gives String buffer to small errors in JSON_PRINTER.

    ORA-06502: PL/SQL : PL/SQL: numeric or value error string: String buffer too small
    ORA-06512: at \"JSON.JSON_PRINTER\", line 13
    ORA-06512: at \"JSON.JSON_PRINTER\", line 211
    ORA-06512: at \"JSON.JSON_PRINTER\", line 238
    ORA-06512: at \"JSON.JSON_PRINTER\", line 229
    ORA-06512: at \"JSON.JSON_PRINTER\", line 238
    ORA-06512: at \"JSON.JSON_PRINTER\", line 256
    ORA-06512: at \"JSON.JSON\", line 265
    ORA-06512: at \"DEV.MY_PACKAGE\", line 1884
    ORA-06512: at line 4124
    ORA-06512: at line 4135
    06502. 00000 - \"PL/SQL: numeric or value error%s\"

    I need to extract the clob from the object to convert it in UTF8 and sending it to my web-service.

  • Martin

    Martin - 2014-04-07

    hello together, i am faced with the same problem.

    Build of large json object with about 10k rows takes 0.5 seconds, but output with the to_clob procedure takes 7 seconds.
    the result clob has about 500 kb.
    htp is not possible in my context.
    we are running on,

    any suggestions ?

    Thanks in advance

    Last edit: Martin 2014-04-07
  • Paul Jones

    Paul Jones - 2015-11-25


    Has anyone managed to speed up outputting the json object to htp.p.

    We are finding major perfformance issues with this and are having to build our json manually.



Log in to post a comment.