title: How to parse a json tree using plsql pljson framework?
body: <p>I am using a webservice <a href="http://maps.google.com/maps/api/geocode/json?address=mysore" rel="nofollow noreferrer">http://maps.google.com/maps/api/geocode/json?address=mysore</a> to get location data in my plsql code. I am able to access the first level data using help from <a href="https://stackoverflow.com/questions/7985841/work-with-json-in-oracle">work with json in oracle</a>. But this provides help to get first level data from json.</p>
  
  <p>I need to further get the lat and lng values. Can anyone help me with this?</p>
  
  <pre><code>Location = {
    "bounds" : {
      "northeast" : {
        "lat" : 44.9483849,
        "lng" : -93.1261959
      },
      "southwest" : {
        "lat" : 44.9223829,
        "lng" : -93.200307
      }
    },
    "location" : {
      "lat" : 44.9330076,
      "lng" : -93.16290629999999
    },
    "location_type" : "APPROXIMATE",
    "viewport" : {
      "northeast" : {
        "lat" : 44.9483849,
        "lng" : -93.1261959
      },
      "southwest" : {
        "lat" : 44.9223829,
        "lng" : -93.200307
      }
    }
  }
  </code></pre>
  
  <p>This is my code to get the address from the google maps api. I need to fetch latitutde, longitude and formatted_address from the response.    </p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE geo_lat_long_addr_proc(
        ADDRESS     VARCHAR2 DEFAULT 'EUR')
       IS
        v_debug_mode        BOOLEAN := TRUE;
        v_req               utl_http.req;
        v_resp              utl_http.resp;
        v_msg               VARCHAR2(80);
        v_entire_msg        VARCHAR2(32767) := NULL;
        v_conversion_factor NUMBER;
        v_url               VARCHAR2(256) :=
                'http://maps.google.com/maps/api/geocode/json?address='||
                ADDRESS
                ;
      BEGIN
  
        v_req := utl_http.begin_request(url =&gt; v_url,
           method =&gt; 'GET');
        v_resp := utl_http.get_response(r =&gt; v_req);
  
        IF    v_debug_mode
        THEN
           dbms_output.put_line('HTTP Status Return code: '||
                           v_resp.status_code);
        END IF;
  
        BEGIN
           LOOP
             utl_http.read_text(r =&gt; v_resp,data =&gt; v_msg);
             v_entire_msg := v_entire_msg||v_msg;
           END LOOP;
        EXCEPTION
           WHEN  utl_http.end_of_body
           THEN  null;
        END;
  
        IF    v_debug_mode
        THEN  dbms_output.put_line(v_entire_msg);
        END IF;
  
        utl_http.end_response(r =&gt; v_resp);
  
      EXCEPTION
        WHEN  others
        THEN  RETURN;
      END geo_lat_long_addr_proc;
      /
  </code></pre>