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)
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY order_entry_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY 0x'0A'
DISABLE_DIRECTORY_LINK_CHECK
FIELDS (json_document CHAR(5000)))
LOCATION (order_entry_dir:'PurchaseOrders.dmp'))
PARALLEL
REJECT LIMIT UNLIMITED;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
CREATE TABLE j_order
(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.
SELECT jt.*
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. -
#PLSQL #ORACLE JASON #ORACLE #ORACLE19c #ORACLE DEVELOPERS