CODEWITHSUNDEEP
jsonoracle-databaseclob
CoreyGlory
CoreyGlory

Reputation: 11

Oracle - Importing JSON from a file

I currently have a Oracle 12c db instance where I'd like to import JSON from a file into a CLOB field.

Is there a proper way to do so?

Thanks

Upvotes: 1

Views: 1617

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

Here's an example loading into a clob or a blob. I'm not saying you should do both, you can choose between the two. Generally we recommend storing JSON in a blob column.

In the example below, "MY_FILES" is a database directory (ie, via the 'create directory' command) where your json file is located.

SQL> declare
  2      l_blob  blob;
  3      l_clob  clob;
  4      l_bfile bfile;
  5  begin
  6      insert into demo values ( 1, empty_blob(), empty_clob() )
  7      returning theBlob, theClob into l_blob, l_clob;
  8
  9      l_bfile := bfilename( 'MY_FILES', 'json.txt' );
 10      dbms_lob.fileopen( l_bfile );
 11      dbms_lob.loadfromfile( l_blob, l_bfile, dbms_lob.getlength( l_bfile ) );
 12      dbms_lob.fileclose( l_bfile );
 13
 14      l_bfile := bfilename( 'MY_FILES', 'json.txt' );
 15      dbms_lob.fileopen( l_bfile );
 16      dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength( l_bfile ) );
 17      dbms_lob.fileclose( l_bfile );
 18  end;
 19  /

Upvotes: 1

Related Questions