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 stackoverflow.com/questions/27122612/… — Arunakiran Nulu
– Arunakiran Nulu, Commented Sep 29, 2016 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
– Chris Poi, Commented Sep 30, 2016 at 11:52

Chris Poi · Accepted Answer · 2016-09-30 14:02:28Z

0

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

answered Sep 30, 2016 at 14:02

Chris Poi

271 gold badge1 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PL/SQL with JSON (list of object) parsing

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related