You Asked

Hello. I have a folder that contains multiple json files. I need to load the contents of each file into a single CLOB column in a table using SQL Loader. Each file will be a separate row in the table.

Ideally, inserting the following files would give me a table with three rows where JSON_DATA contains the data from the file.

foldername/

file1.json
file2.json
file3.json

Here is the table info:

  CREATE TABLE "JSON_DATA"
   (
 "JSON_DATA" CLOB
   )

The following control file loads the first character (i.e. '{') of each json file and that is it. I need the entire file loaded.

--ctl file

LOAD DATA
INFILE 'foldername/*.json'
TRUNCATE
INTO TABLE JSON_DATA
(HE_JSON_DATA)

This problem seems pretty straightforward, but everything I've tried doesn't work.

and Chris said...

SQL*Loader has these defaults:

TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

This means it considers double quotes to be the start/end of fields. Given there are almost certainly double quotes somewhere in the JSON fields, this is a problem!

To get around this specify your own values for the terminator characters! e.g.:

FIELDS TERMINATED BY '##'

If the JSON documents are formatted/split over many lines you'll need further work to combine them into one. One way is with the LOBFILE option

https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:627223659651

Rating

(1 rating)

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

Comments

why not External tables?

Rajeshwaran Jeyabal, February 27, 2023 - 3:07 pm UTC

Since you are using 21c, why not use a feature called "Inline External" tables ?

[oracle@en702c101vm01 PRODES]$ pwd
/oracleexabkp/PRODES
[oracle@en702c101vm01 PRODES]$ ls -ltr
total 1840
-rw-r--r-- 1 oracle oinstall 465548 Oct 29 02:20 providerJson_01.txt
-rw-r--r-- 1 oracle oinstall 465548 Oct 29 02:20 providerJson_02.txt
-rw-r--r-- 1 oracle oinstall 465548 Oct 29 02:20 providerJson_03.txt
-rw-r--r-- 1 oracle oinstall 465548 Oct 29 02:20 providerJson.txt
-rwxr-xr-x 1 oracle oinstall     61 Oct 29 02:31 list.txt
[oracle@en702c101vm01 PRODES]$ cat list.txt
providerJson_01.txt
providerJson_02.txt
providerJson_03.txt

c##rajesh@PRODES> col json_data format a40 trunc
c##rajesh@PRODES> select *
  2  from external(
  3      ( c1 number,
  4        json_data clob )
  5      type oracle_loader
  6      default directory TMP
  7      access parameters(
  8          records delimited by newline
  9          nologfile
10          nobadfile
11          fields terminated by ','
12          (  c1 recnum,
13             c2 char(80) )
14      column transforms (json_data from lobfile (c2) from (TMP) clob)
15          )
16          location (TMP:'list.txt')
17          reject limit unlimited
18  ) ;

        C1 JSON_DATA
---------- ----------------------------------------
         1 {
         2 {
         3 {

c##rajesh@PRODES>
c##rajesh@PRODES> create table json_test( id number,
  2      the_json clob
  3      check (the_json is json) );

Table created.

c##rajesh@PRODES> set timing on
c##rajesh@PRODES> insert into  json_test( id, the_json)
  2  select c1,json_data
  3  from external(
  4      ( c1 number,
  5        json_data clob )
  6      type oracle_loader
  7      default directory TMP
  8      access parameters(
  9          records delimited by newline
10          nologfile
11          nobadfile
12          fields terminated by ','
13          (  c1 recnum,
14             c2 char(80) )
15      column transforms (json_data from lobfile (c2) from (TMP) clob)
16          )
17          location (TMP:'list.txt')
18          reject limit unlimited
19  );

3 rows created.

Elapsed: 00:00:00.33
c##rajesh@PRODES> set timing off
c##rajesh@PRODES> commit;

Commit complete.

c##rajesh@PRODES> select id, dbms_lob.getlength( the_json ) from json_test;

        ID DBMS_LOB.GETLENGTH(THE_JSON)
---------- ----------------------------
         1                       465548
         2                       465548
         3                       465548

c##rajesh@PRODES>

February 27, 2023 - 5:25 pm UTC

Good question, though to use external tables you have to make the files accessible to the database server. This is not always practical.

With SQL*Loader you can load from files on your machine.

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

why not External tables?

More to Explore

Utilities

SQL Loader: Loading JSON file data into single column with a new row per each file in a folder

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

why not External tables?

More to Explore

Utilities