PL/SQL with JSON (list of object) parsing

Question

Since I am writing, there is something that I am not able to understand. I have the following JSON object data that is indented. I would like to retrieve the values that are associated with “industry” and “exchange” for each “response” in PL/SQL in Oracle 12. I have installed the JSON package found in the forum https://github.com/pljson/pljson. All solution that I have found were not indented, so I am not sure if this is the reason, for not being able to access the information. Any suggestion or article that could help me would be greatly appreciated. Thanks in advance.

This is my JSON string

{  
   "response":{  
      "MSFT":{  
         "meta":{  
            "status":"ok"
         },
         "results":{  
            "industry":{  
               "data":"Software - Infrastructure",
               "meta":{  
                  "status":"ok"
               }
            },
            "exchange":{  
               "data":"NASDAQ",
               "meta":{  
                  "status":"ok"
               }
            }
         }
      },
      "AAPL":{  
         "meta":{  
            "status":"ok"
         },
         "results":{  
            "industry":{  
               "data":"Consumer Electronics",
               "meta":{  
                  "status":"ok"
               }
            },
            "exchange":{  
               "data":"NASDAQ",
               "meta":{  
                  "status":"ok"
               }
            }
         }
      }
   },
   "meta":{  
      "status":"ok",
      "url":"http://testdata.com/api/v3/companies/AAPL,MSFT/info/exchange,industry"
   }
}

This is a modified sample code that I have found on the forum base on this link

Parse JSON into Oracle table using PL/SQL

create or replace procedure json4(w_periode in varchar2) is
begin
  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;
obj json := json();
arr json_list := json_list();


BEGIN
 -- service's input parameters
 -- ...set input parameters
 -- UTL_HTTP.write_text(l_http_request, l_param_list);
 l_response_text := '{"response": {"MSFT": {"meta": {"status": "ok"}, "results": {"industry": {"data": "Software - Infrastructure", "meta": {"status": "ok"}}, "exchange": {"data": "NASDAQ", "meta": {"status": "ok"}}}}, "AAPL": {"meta": {"status": "ok"}, "results": {"industry": {"data": "Consumer Electronics", "meta": {"status": "ok"}}, "exchange": {"data": "NASDAQ", "meta": {"status": "ok"}}}}}, "meta": {"status": "ok", "url": "http://testurl.com"}}';

/*  -- 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);
  */
    obj := json(l_response_text);



  DBMS_OUTPUT.put_line(l_response_text);
 /* l_list := json_list(l_response_text);*/
 /*arr := json_list(obj.get('industry'));*/

end;
end json4;

take a look at below link http://stackoverflow.com/questions/27122612/parse-json-into-oracle-table-using-pl-sql — Arunakiran Nulu, Sep 29 '16 at 20:47
This is the exact same link I have put in my question, It seem's to only handle array and not object list. — Chris Poi, Sep 30 '16 at 11:52

score 0 · Answer 1 · answered Sep 30 '16 at 14:02

I found a solution. The link in the post need's to be bracket as an array. In my varchar2 I had to insert the "[" symbol at beginning and at the end. The problem is that it will only parse the JSON if it is not indented, from my understanding.

I then install the APEX5 package, it was able to handle indented code. I follow the installation procedure https://www.youtube.com/watch?v=JGnkPVq7V98 and on the first try it work perfectly.

Hope this help

PL/SQL with JSON (list of object) parsing

1 Answers1