store json objects in oracle table

2009-12-28
2013-05-28
  • 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

        declare
          obj json;
          my_clob clob := empty_clob();
        begin
          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); 
       
          commit;
        end;
        /

    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.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks