CODEWITHSUNDEEP
jsonoracle-databaseinsert
user2438439
user2438439

Reputation: 21

How do I insert JSON data into an Oracle database?

I am trying to insert this sample json data into an Oracle version 19 table. I tried this without any success:

DECLARE 
  myJSON  JSON_ARRAY_T;
  SET myJSON := 
    json_array_t ('
      [
        {"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 (CUST_NUM, SORT_ORDER, CATEGORY)
  SELECT CUST_NUM, SORT_ORDER, CATEGORY 
  FROM OPENJSON(myJSON)
  WITH (
   CUST_NUM int, 
   SORT_ORDER int, 
   CATEGORY VARCHAR2
  );
END;

Thank you for your time.

Upvotes: 1

Views: 10877

Answers (1)

Chris Saxon
Chris Saxon

Reputation: 9805

If you're trying to split the JSON document to rows-and-columns, the easiest way is to process it as a string and use json_table to extract out the relevant parts.

For example:

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

DECLARE 
  myJSON  varchar2(1000) := '[
  {"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; 
/

select * from jt_test;

CUST_NUM  SORT_ORDER CATEGORY    
   12345           1 ICE CREAM    
   12345           2 ICE CREAM    
   12345           3 ICE CREAM

Upvotes: 3

Related Questions