-- Oracle 12c: Parse JSON Content to Insert Data Into Table 


CREATE TABLE sales_json_data
(sales_tran_id     NUMBER(16) GENERATED BY DEFAULT AS IDENTITY 
                   (START WITH 1 INCREMENT BY 1) PRIMARY KEY,
 invoice_num       VARCHAR2(30),
 invoice_date      DATE,
 invoice_amt       NUMBER(16,2),
 invoice_line      NUMBER(5),
 created_date      TIMESTAMP(3) DEFAULT SYSTIMESTAMP
);


INSERT INTO  sales_json_data
(invoice_num,
 invoice_date,
 invoice_amt,
 invoice_line
)
SELECT
 JSON_VALUE(qt.json_data,'$.invoice_no'),
 TO_DATE(JSON_VALUE(qt.json_data,'$.invoice_date'),'Mon-dd-yyyy'), 
 JSON_VALUE(qt.json_data,'$.invoice_amt'),
 JSON_VALUE(qt.json_data,'$.line_no')
FROM
(SELECT
   '{invoice_no:"I00000001",
     invoice_amt:1001.00,
     invoice_date:"Jan-14-2017",
     line_no: 1
    }' as json_data
 FROM DUAL
) qt
;