How do I insert big JSON data into an Oracle database(insert 15000 rows with 1 query)?

Question

I have an API that gives me 15000 rows of data in json every 10 seconds. I use this tutorial and when I insert 5 rows or more inserting is OK, but when I insert 15000 row that get error PLS-00172: string literal too long. My Code:

 create table jt_test (
  CUST_NUM int, SORT_ORDER int, CATEGORY varchar2(100)
);

DECLARE 
  myJSON  CLOB := '[
  {"CUST_NUM": 12345, "SORT_ORDER": 1, "CATEGORY": "ICE CREAM"},
  {"CUST_NUM": 12345, "SORT_ORDER": 2, "CATEGORY": "ICE CREAM"},
  {"CUST_NUM": 12345, "SORT_ORDER": 3, "CATEGORY": "ICE CREAM"}
]';

BEGIN
  insert into jt_test
    select * from json_table ( myjson, '$[*]'
      columns ( 
        CUST_NUM, SORT_ORDER, CATEGORY
      )
    );
END;

Notes: I before use MSSql this link and work perfect even for 20000 rows in one query.

I use Oracle19C and connect and insert to db with cx_Oracle module python

Edit: I test this tutorial but not work.

Does this answer your question? ["PLS-00172: string literal too long" error is thrown while setting large string value to CLOB](https://stackoverflow.com/questions/64152987/pls-00172-string-literal-too-long-error-is-thrown-while-setting-large-string) — Ouroborus, Jul 29 '22 at 16:36
Are you sure you read the answers? The basic solution is to cut up your string literal into smaller chunks. — Ouroborus, Jul 29 '22 at 17:08

score 1 · Accepted Answer · answered Jul 29 '22 at 17:06

Instead of creating a large literal string in PL/SQL (which isn't allowed) you can create that string in Python instead and simply pass it through. Something like this should do the trick:

long_json_string = '''[
    {"CUST_NUM": 12345, "SORT_ORDER": 1, "CATEGORY": "ICE CREAM"},
    {"CUST_NUM": 12345, "SORT_ORDER": 2, "CATEGORY": "ICE CREAM"},
    {"CUST_NUM": 12345, "SORT_ORDER": 3, "CATEGORY": "ICE CREAM"}
  ]'''

sql = '''
  declare
    myJSON CLOB := :long_json_string;
  begin
    insert into jt_test
      select * from json_table ( myjson, '$[*]'
        columns ( 
          CUST_NUM, SORT_ORDER, CATEGORY
        )
      );
  END;'''

cursor.execute(sql, long_json_string=long_json_string)

How do I insert big JSON data into an Oracle database(insert 15000 rows with 1 query)?

1 Answers1

Linked