Oracle - Tipo de dato json y json list

   
Vista:

Tipo de dato json y json list

Publicado por Angel Ballestereos (13 intervenciones) el 21/12/2015 22:56:54
Cuales son los los requisitos basicos para trabjar con json.

El siguiente codigo me muestra como funcionar con json:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
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;

sin embargo al momento de ejecutarlo, me dice que no renoce JSON como tipo de dato.

De antemano, muchas gracias.
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder