Load External JASON data in Oracle

Updated: Apr 9

Uploading Data files in oracle is nothing new. Many of us have been doing it for years but playing with JASON files is something that intrigues. Umpteen number of blogs are scattered everywhere which talks about uploading .dmp files but very few provides the actual .dmp file that can be uploaded.

This post walks through the process to upload the external Jason data in oracle and then query it. The use cases in the blog makes the step simpler. I have used my windows machine to make it still easy.

Create directory in Oracle.

SQL> conn sys@pdb1 as sysdba;
SQL>   CREATE OR REPLACE DIRECTORY order_entry_dir AS 'C:\ORACLE\PRODUCT\19\bin\order_dir'; (this workaround is for local machine running on windows for linux you need to have a different path)

Directory created

SQL> GRANT READ, WRITE ON DIRECTORY order_entry_dir to hr;
Grant succeeded

Please ensure actual file path mentioned in the above query (C:\ORACLE\PRODUCT\19\bin\order_dir) is present on windows machine and .it hold the .dmp file

Please find the Link to download the purchase_order.dmp file . Make sure this file is present at the desired location before you proceed further.

Create an External table and upload it with the .dmp file

CREATE TABLE json_dump_file (json_document BLOB)
                         ACCESS PARAMETERS
                           (RECORDS DELIMITED BY 0x'0A'
                            FIELDS (json_document CHAR(5000)))
                         LOCATION (order_entry_dir:'PurchaseOrders.dmp'))

Once the table is Created we are all set to upload the contents of external table to a JASON_TABLE that has primary keyid and JSON column po_document, using data .

Creating a table with JASON Column and load it with the data of external table

  (id          VARCHAR2 (32) NOT NULL PRIMARY KEY,
   date_loaded TIMESTAMP (6) WITH TIME ZONE,
   po_document BLOB
   CONSTRAINT ensure_json CHECK (po_document IS JSON))
  LOB (po_document) STORE AS (CACHE);
  INSERT INTO  j_order (id, date_loaded, po_document)
  SELECT SYS_GUID(), SYSTIMESTAMP, json_document FROM json_dump_file
    WHERE json_document IS JSON;

Querying the Data.

  FROM j_order,
       json_table(po_document, '$'
         COLUMNS (
           reference VARCHAR2(32 CHAR) PATH '$.Reference',
           requestor VARCHAR2(32 CHAR) PATH '$.Requestor', 
           NESTED                      PATH '$.ShippingInstructions.Phone[*]'
             COLUMNS (phone_type VARCHAR2(32 CHAR) PATH '$.type',
                      phone_num  VARCHAR2(20 CHAR) PATH '$.number'))) AS "JT";
  1	TGATES-20140517	Timothy Gates	Office	408-555-6201
2	DOCONNEL-20141107	Donald OConnell	Office	276-555-7094
3	DOCONNEL-20141109	Donald OConnell	Office	9-555-1891
4	DOCONNEL-20141112	Donald OConnell	Office	14-555-8222
5	TGATES-20140503	Timothy Gates	Office	726-555-2816
6	TGATES-20140503	Timothy Gates	Office	267-85-311
7	TGATES-20140504	Timothy Gates	Office	984-555-6025
8	MSULLIVA-20141102	Martha Sullivan	Office	979-555-6598
9	MSULLIVA(-20141113	Martha Sullivan	Office	716-555-2648
10	TRAJS-20140518	Trenna Rajs	Office	905-555-5489
11	TRAJS-20140520	Trenna Rajs	Office	741-2-5738
12	MSULLIVA-20141121	Martha Sullivan	Office	928-555-8133
13	TRAJS-20140530	Trenna Rajs	Office	382-555-8799
14	VJONES-20140503	Vance Jones	Office	713-555-2743
15	VJONES-20140504	Vance Jones	Office	632-555-9938
16	AHUNOLD-20141130	Alexander Hunold	Office	784-555-7990
17	STOBIAS-20140515	Sigal Tobias	Office	710-555-7994
18	STOBIAS-20140516	Sigal Tobias	Office	988-555-9311
19	SVOLLMAN-20140502	Shanta Vollman	Office	52-555-544
20	SVOLLMAN-20140524	Shanta Vollman	Office	222-555-5439
21	SVOLLMAN-20140525	Shanta Vollman	Office	823-555-9969
22	SVOLLMAN-20140506	Shanta Vollman	Office	846-555-1638
23	SVOLLMAN-20140531	Shanta Vollman	Office	628-81-6898

To get more on Oracle JASON , please go through my previous blog.


  • Facebook
  • Twitter
  • LinkedIn