Parse JSON into Oracle table using PL/SQL
Since this question scores high in results, I want to post this preferred alternative:
Oracle has released APEX 5.0 (April 15. 2015). With it you get access to a great API to work with JSON
I'm using it on 11.2 and have been able to crunch every single json, from simple to very complex objects with multiple arrays and 4/5 levels. APEX_JSON
If you do not want to use APEX. Simply install the runtime environment to get access to the API.
Sample usage, data from json.org's example :
declare sample_json varchar2 (32767) := '{ "glossary": { "title": "example glossary", "GlossDiv": { "title": "S", "GlossList": { "GlossEntry": { "ID": "SGML", "SortAs": "SGML", "GlossTerm": "Standard Generalized Markup Language", "Acronym": "SGML", "Abbrev": "ISO 8879:1986", "GlossDef": { "para": "A meta-markup language, used to create markup languages such as DocBook.", "GlossSeeAlso": ["GML", "XML"] }, "GlossSee": "markup" } } } }}';begin apex_json.parse (sample_json); dbms_output.put_line (apex_json.get_varchar2 ('glossary.GlossDiv.title')); dbms_output.put_line (apex_json.get_varchar2 ('glossary.GlossDiv.GlossList.GlossEntry.GlossTerm')); dbms_output.put_line (apex_json.get_varchar2 ('glossary.GlossDiv.GlossList.GlossEntry.GlossDef.GlossSeeAlso[%d]', 2));end;Result:PL/SQL block executed
SStandard Generalized Markup LanguageXML
I used PL/JSON library. Specifically, JSON_EXT package functions in order to parse it.
The following script inspired by Oracle Community answer worked for me
DECLARE l_param_list VARCHAR2(512); l_http_request UTL_HTTP.req; l_http_response UTL_HTTP.resp; l_response_text VARCHAR2(32767);l_list json_list;A_id VARCHAR2(200);UserId VARCHAR2(100);UserName VARCHAR2(100);OutletCode VARCHAR2(100);OutletName VARCHAR2(100);MobileNumber VARCHAR2(100);PhoneNumber VARCHAR2(100);Address VARCHAR2(100);City VARCHAR2(100);State VARCHAR2(100);Postcode VARCHAR2(100);Email VARCHAR2(100);UpdateCount VARCHAR2(100);loginCount VARCHAR2(100);ReferencePhoto VARCHAR2(100);Updates VARCHAR2(100);AccountLocked VARCHAR2(100);Oracle_Flag VARCHAR2(100);acl VARCHAR2(100);BEGIN -- service's input parameters -- preparing Request... l_http_request := UTL_HTTP.begin_request('https://api.appery.io/rest/1/db/collections/Outlet_Details?where=%7B%22Oracle_Flag%22%3A%22Y%22%7D' , 'GET' , 'HTTP/1.1'); -- ...set header's attributes UTL_HTTP.set_header(l_http_request, 'X-Appery-Database-Id', '53f2dac5e4b02cca64021dbe'); --UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_param_list)); -- ...set input parameters -- UTL_HTTP.write_text(l_http_request, l_param_list); -- get Response and obtain received value l_http_response := UTL_HTTP.get_response(l_http_request); UTL_HTTP.read_text(l_http_response, l_response_text); DBMS_OUTPUT.put_line(l_response_text); l_list := json_list(l_response_text);FOR i IN 1..l_list.countLOOP A_id := json_ext.get_string(json(l_list.get(i)),'_id'); UserId := json_ext.get_string(json(l_list.get(i)),'UserId'); UserName := json_ext.get_string(json(l_list.get(i)),'UserName'); OutletCode := json_ext.get_string(json(l_list.get(i)),'OutletCode'); OutletName := json_ext.get_string(json(l_list.get(i)),'OutletName'); MobileNumber := json_ext.get_string(json(l_list.get(i)),'MobileNumber'); PhoneNumber := json_ext.get_string(json(l_list.get(i)),'PhoneNumber'); Address := json_ext.get_string(json(l_list.get(i)),'Address'); City := json_ext.get_string(json(l_list.get(i)),'City'); State := json_ext.get_string(json(l_list.get(i)),'State'); Postcode := json_ext.get_string(json(l_list.get(i)),'Postcode'); Email := json_ext.get_string(json(l_list.get(i)),'Email'); UpdateCount := json_ext.get_string(json(l_list.get(i)),'UpdateCount'); loginCount := json_ext.get_string(json(l_list.get(i)),'loginCount'); ReferencePhoto := json_ext.get_string(json(l_list.get(i)),'ReferencePhoto'); Updates := json_ext.get_string(json(l_list.get(i)),'Updates'); AccountLocked := json_ext.get_string(json(l_list.get(i)),'AccountLocked'); Oracle_Flag := json_ext.get_string(json(l_list.get(i)),'Oracle_Flag'); acl := json_ext.get_string(json(l_list.get(i)),'acl');insert .....Notice that json_ext.get_string retuns only VARCHAR2 limited to 32767 max. In order to use the same package with larger json_list and json_values (>32KB) check here.
If you have APEX 5.0 and above, better option and much better performance via APEX_JSON package. See @Olafur Tryggvason's answer for details
Orace provides PL/SQL DOM APIs for JSON handling. Strongly recommend using it, as it provides tons of useful APIs.