store json objects in oracle table

  • dave renner

    dave renner - 2009-12-28

    can someone provide working example code to store a pl/json json object in an oracle table …. I have tried but no success

    thank you

  • Jonas Krogsboell

    Hi Drenner,

    I have also tried to accomplish that, but when the json object became a little complicated, it just stopped working. I think it's an oracle bug, but don't know if it has been fixed in the lastest patch. Right now you can use the Clob methods to store the json in a table.

    Unfortunately, I deleted the code that caused the problem so I can't even show it to you right now. Next year, I will install a 11g db to do json test. Hopefully the problem has been fixed in that version.

  • dave renner

    dave renner - 2010-01-04

    as I am new to json and oracle do you have an example of code that illustrates how to store json objects in oracle table using the clob type ? I would really appreciate it.

    thank you so much

  • Jonas Krogsboell

          obj json;
          my_clob clob := empty_clob();
          obj := json();
          -populate json obj somehow
          dbms_lob.createtemporary(my_clob, true);
          obj.to_clob(my_clob, true);
          insert into yourjsonclobtable (J_ID, J_CLOB)
          values (1, my_clob); 

    Also see example/ex11.sql - and google how to handle clob's

    If your json-text is small, you can store it in a varchar2 (limited to 4000 bytes in a table). Perhaps a plsql-varchar2 (limited to 32676 bytes) can be stored in a clob in a table - but I don't know that for sure.

    The output methods of the json and json_list objects can suppress whitespace if you provide false as an argument. Just browse the documentation and see how it works.

  • dave renner

    dave renner - 2010-01-04

    thank you so much … i will try this


Log in to post a comment.