Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Prashanth.

Asked: February 25, 2017 - 8:38 am UTC

Last updated: October 22, 2021 - 3:49 am UTC

Version: Oracle 12c

Viewed 10K+ times! This question is

You Asked

Hi Ask Tom Team,

I have a requirement to load JSON file into a table in Oracle database.

Have seen some examples using External Table and filling it from a JSON File & then extracting fields through JSON functions to load data into relational table.
Need your suggestion on any other available options .

Thanks,
Prashanth

and Connor said...

A json is just a clob

SQL> host cat c:\temp\json.dat
{
    "glossary": {
        "title": "example glossary",
    "GlossDiv": {
            "title": "S",
      "GlossList": {
                "GlossEntry": {
                    "ID": "SGML",
          "SortAs": "SGML",
          "GlossTerm": "Standard Generalized Markup Language",
          "Acronym": "SGML",
          "Abbrev": "ISO 8879:1986",
          "GlossDef": {
                        "para": "A meta-markup language, used to create markup languages such as DocBook.",
            "GlossSeeAlso": ["GML", "XML"]
                    },
          "GlossSee": "markup"
                }
            }
        }
    }
}

SQL> create table t ( my_json clob, constraint chk check ( my_json is json ));

Table created.

SQL> declare
  2    b bfile := bfilename('TEMP','json.dat');
  3    c clob;
  4  begin
  5    dbms_lob.open(b);
  6    dbms_lob.createtemporary(c,true);
  7
  8    dbms_lob.loadfromfile(c,b,dbms_lob.getlength(b));
  9    insert into t values (c);
 10    commit;
 11    dbms_lob.freetemporary(c);
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t;

MY_JSON
--------------------------------------------------------------------------------
{
    "glossary": {
        "title": "example glossary",
    "GlossDiv": {
            "title": "S",
      "GlossList": {
                "GlossEntry": {
                    "ID": "SGML",
          "SortAs": "SGML",
          "GlossTerm": "Standard Generalized Markup Language",
          "Acronym": "SGML",
          "Abbrev": "ISO 8879:1986",
          "GlossDef": {
                        "para": "A meta-markup language, used to create markup l
anguages such as DocBook.",
            "GlossSeeAlso": ["GML", "XML"]
                    },
          "GlossSee": "markup"
                }
            }
        }
    }
}


Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

dbms_lob.close ?!?!?!

Rajeshwaran, Jeyabal, February 25, 2017 - 10:43 am UTC

Team,

Any reason to miss the " dbms_lob.close(b); " in the above program?

since it is a "BFILE" - so closing them using DBMS_LOB doesn't matter much ?

demo@ORA12C> create table t (x clob check(x IS JSON) );

Table created.

demo@ORA12C> declare
  2     l_file bfile := bfilename('DATA_PUMP_DIR','json.dat');
  3     c clob;
  4  begin
  5     dbms_lob.open(l_file);
  6     dbms_lob.createtemporary(c,true);
  7     dbms_lob.loadfromfile(c,l_file,
  8             dbms_lob.getlength(l_file) );
  9     insert into t(x) values(c);
 10     commit;
 11     dbms_lob.close(l_file);
 12     dbms_lob.freetemporary(c);
 13  end;
 14  /

PL/SQL procedure successfully completed.


Connor McDonald
February 26, 2017 - 1:59 am UTC

Tardiness on my part - also the brevity of my answer due to an inclination to match the brevity of detail in the question :-)

Follow up last post

A reader, February 26, 2017 - 6:34 am UTC

You could reply. It was lunch time and I had closed the session afterwhile😎

"All files opened during a user session are implicitly closed at the end of the session. However, Oracle strongly recommends that you close the files after both normal and abnormal termination of operations on the BFILE."

Binish George, June 05, 2019 - 3:48 pm UTC

Hi,

I tried to load the json file using the method explained here but when I check the table I see the data has been loaded in a different language altogether (chinese I guess). Not sure why this is happening. Any thoughts?

Binish
Chris Saxon
June 06, 2019 - 4:48 pm UTC

A character set conversion problem at a guess. But you'd need to share details of what exactly has happened for us to give effective help...

Very informative post

John, June 11, 2019 - 11:38 am UTC

I like this post. It is very useful for me. It is giving more information and ideas for my work. I hope you will keep this website updated so users can come and read interesting stories. I am very impressed by the way you share nice and valuable content.

Thanks Regards
https://esmartprints.com/product-category/pop-up-banners/

JSON_TRANSFORM in Oracle 21c

Rajeshwaran, Jeyabal, October 19, 2021 - 6:08 am UTC

Team,

Given this JSON, how do i increment the quantity by two in each level?

tried the below using json_transform but ended up with error.

is that possible using json_transform function? kinldy help.
demo@XEPDB1> select json_serialize(y pretty) y
  2  from t
  3  where x =2;

Y
----------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    }
  ]
}


demo@XEPDB1> select json_serialize(
  2     json_transform( y, set '$.produce[*].quantity' =
  3             '$.produce[*].quantity' + 2 ) pretty )
  4  from t
  5  where x =2;
                '$.produce[*].quantity' + 2 ) pretty )
                *
ERROR at line 3:
ORA-01722: invalid number


demo@XEPDB1>

Connor McDonald
October 21, 2021 - 1:56 am UTC

Picking loosely related questions in order to post a new question in is not how the site works, because we lose the granularity for future community members to find answers.

(But yes it can be done)

JSON_TRANSFORM in Oracle 21c

Rajeshwaran, Jeyabal, October 19, 2021 - 7:08 am UTC

Team,

Was able to get this done using JSON object types from PL/SQL.

would like to know if the same can be done using JSON_TRANSFROM function in sql?
demo@XEPDB1> create or replace function update_json( p_input json )
  2  return json
  3  as
  4      l_data json_object_t;
  5      l_size number := 0;
  6      l_ele json_element_t;
  7      l_array json_array_t;
  8      l_obj json_object_t;
  9      l_qty number := 0;
 10  begin
 11      l_data := json_object_t( p_input );
 12
 13      if l_data.has('produce') then
 14          l_ele := l_data.get('produce');
 15          if l_ele.is_array then
 16              l_size := l_ele.get_size()-1;
 17              l_array := json_array_t( l_ele );
 18              for i in 0..l_size
 19              loop
 20                  l_obj := treat( l_array.get(i) as json_object_t );
 21                  l_qty := l_obj.get_Number('quantity');
 22                  l_obj.put( 'quantity', l_qty+2 );
 23              end loop;
 24          end if;
 25      end if;
 26      return l_data.to_json;
 27  end;
 28  /

Function created.

demo@XEPDB1> select json_serialize(y)
  2  from t t1
  3  where x =2;

JSON_SERIALIZE(Y)
--------------------------------------------------------------------------------------
{"produce":[{"fruit":"apple","quantity":10},{"fruit":"orange","quantity":15}]}

demo@XEPDB1> select update_json(y)
  2  from t t1
  3  where x =2;

UPDATE_JSON(Y)
--------------------------------------------------------------------------------
{"produce":[{"fruit":"apple","quantity":12},{"fruit":"orange","quantity":17}]}

demo@XEPDB1>

Connor McDonald
October 21, 2021 - 1:56 am UTC

Picking loosely related questions in order to post a new question in is not how the site works, because we lose the granularity for future community members to find answers.

JSON_TRANSFORM in Oracle 21c

Rajeshwaran Jeyabal, October 21, 2021 - 3:52 am UTC

Thanks Connor, will take care of this in future and will make this as a new question for further follow up.
Connor McDonald
October 22, 2021 - 3:49 am UTC

Appreciate your understanding

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here