Oracle - Tipo de dato json y json list

CREATE OR REPLACE PROCEDURE geocode_address(p_address IN VARCHAR2) IS

  l_obj           json;

  l_results       json_list;

  l_tempobj       json;

  l_addr_comps    json_list;

  l_addr          json;

  l_typesarr      json_list;

  l_geom_obj      json;

  l_loc           json;

  l_status        VARCHAR2(255);

  l_types         VARCHAR2(30);

  l_short_name    VARCHAR2(200);

  l_street_number VARCHAR2(200);

  l_street        VARCHAR2(200);

  l_city          VARCHAR2(200);

  l_state         VARCHAR2(30);

  l_zip           VARCHAR2(10);

  l_county        VARCHAR2(30);

  l_country       VARCHAR2(10);

  l_tries         PLS_INTEGER := 0;

  l_address       VARCHAR2(4000);

  l_request       VARCHAR2(4000);

  l_pieces        utl_http.html_pieces;

  l_json          CLOB;

  l_lat           VARCHAR2(40);

  l_lng           VARCHAR2(40);

  e_query_limit  EXCEPTION;

  e_zero_results EXCEPTION;

  l_prc CONSTANT VARCHAR2(16) := 'GEOCODE ADDRESS';

BEGIN

  dbms_application_info.set_module(module_name => l_prc, action_name => 'START');

  -- replace empty spaces with +

  l_address := REPLACE(TRIM(p_address), ' ', '+');

  BEGIN

    l_request := 'http://maps.googleapis.com/maps/api/geocode/json?address=' ||

                 l_address || chr(38) || 'sensor=false';

    l_tries  := 0;

    l_status := NULL;

    l_json   := NULL;

    -- Try three gets in case of OVER_QUERY_LIMIT errors

    WHILE nvl(l_status, '*') != 'OK' AND l_tries < 3

    LOOP

      dbms_application_info.set_client_info(client_info => 'HTTP REQUEST try ' ||

                                                           l_tries);

      -- JSON object may be long, so split it into pieces

      l_pieces := utl_http.request_pieces(l_request);

      FOR i IN 1 .. l_pieces.count

      LOOP

        l_json := l_json || l_pieces(i);

      END LOOP;

      -- Create json object from google response

      l_obj    := json(l_json);

      -- Check the response status. If it is OK then proceede with parsing.

      l_status := json_ext.get_string(l_obj, 'status');

      IF l_status != 'OK'

      THEN

        dbms_output.put_line(l_prc || '  status [' || l_status || ']');

        IF l_status = 'OVER_QUERY_LIMIT' AND l_tries >= 3

        THEN

          dbms_output.put_line(l_prc || ': query limit reached. Exiting');

          RAISE e_query_limit;

        ELSIF l_status = 'ZERO_RESULTS'

        THEN

          RAISE e_zero_results;

        ELSE

          l_tries := l_tries + 1;

          dbms_lock.sleep(2);

        END IF;

      END IF;

      --dbms_output.put_line(l_json);

      l_json := NULL;

    END LOOP;

    BEGIN

      dbms_application_info.set_client_info(client_info => 'PARSE JSON');

      -- The overall JSON is an array named results

      l_results := json_list(l_obj.get('results'));

      -- There is only a single element in the results array, so get the first (and last) one

      l_tempobj := json(l_results.get(1));

      -- The next level contains an array named address_components

      l_addr_comps := json_list(l_tempobj.get(1));

      -- loop through the address components and test the types array for address elements

      FOR i IN 1 .. l_addr_comps.count

      LOOP

        l_addr := json(l_addr_comps.get(i));

        --l_addr.print;

        l_typesarr := json_list(l_addr.get('types'));

        -- Types is not a json array, but a string array so we have to get

        -- the first element using the types[1] syntax

        l_types      := json_ext.get_string(l_addr, 'types[1]');

        l_short_name := json_ext.get_string(l_addr, 'short_name');

        CASE l_types

          WHEN 'street_number' THEN

            l_street_number := l_short_name;

          WHEN 'route' THEN

            l_street := l_short_name;

          WHEN 'locality' THEN

            l_city := l_short_name;

          WHEN 'administrative_area_level_1' THEN

            l_state := l_short_name;

          WHEN 'administrative_area_level_2' THEN

            l_county := l_short_name;

          WHEN 'postal_code' THEN

            l_zip := l_short_name;

          WHEN 'country' THEN

            l_country := l_short_name;

          ELSE

            NULL;

        END CASE;

      END LOOP;

      -- now get lat/lng

      l_geom_obj := json(l_tempobj.get(3));

      --l_geom_obj.print;

      l_loc := json_ext.get_json(l_geom_obj, 'location');

      --l_loc.print;

      l_lat := to_char(json_ext.get_number(l_loc, 'lat'));

      l_lng := to_char(json_ext.get_number(l_loc, 'lng'));

      dbms_output.put_line('Address: ' || l_street_number || ' ' ||

                           l_street || ' ' || l_city || ' ' || l_state || ' ' ||

                           l_zip || ' ' || l_county || ' ' || l_country);

      dbms_output.put_line('lat/lng: ' || l_lat || ' ' || l_lng);

    EXCEPTION

      WHEN e_query_limit THEN

        raise_application_error(-20001, 'GOOGLE QUERY LIMIT REACHED');

      WHEN OTHERS THEN

        dbms_output.put_line(l_prc || ': ' || SQLERRM);

    END;

  EXCEPTION

    WHEN e_zero_results THEN

      dbms_output.put_line(l_prc || ': Zero Results. Getting next address');

  END;

  COMMIT;

  dbms_application_info.set_client_info(client_info => 'DONE');

EXCEPTION

  WHEN OTHERS THEN

    RAISE;

END geocode_address;