Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

PL JSON clob

2013-02-20
2014-04-07
  • 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.

     
  • 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.

    /Jonas

     
  • 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\"
    Cause:
    Action:

    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 11.2.0.1,

    any suggestions ?

    Thanks in advance

     
    Last edit: Martin 2014-04-07