How to Parse JSON in Oracle SQL? (Version:11.2.0)

Support for JSON in Oracle 11g

No, JSON support was introduced in Oracle database 12c release 2 (12.1.0.2)

Regards

Identifier 'APEX_JSON.PARSE' must be declared in oracle 11.2.0

This depends on the APEX version within your database, which I'm guessing may still be 4.x on your 11.2 instance.

SELECT VERSION_NO FROM APEX_RELEASE;

The apex_json package was introduced with 5.0
http://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_json.htm#AEAPI29635

work with json in oracle

I have started using this library, and it seems promising:
https://github.com/pljson/pljson

Easy to install, and the examples are good.

To use the library in your example, add these variables to your procedure..

mapData     json;
results     json_list;
status      json_value;
firstResult json;
geometry    json;

....

Then you can manipulate the response as a json object.

-- convert the result from the get to a json object, and show some results.
mapData := json(v_ans);

-- Show the status of the request
status := mapData.get('status');
dbms_output.put_line('Status = ' || status.get_string());

IF (status.get_string() = 'OK') THEN
  results := json_list(mapData.get('results'));
  -- Grab the first item in the list
  resultObject := json(results.head);

  -- Show the human readable address 
  dbms_output.put_line('Address = ' || resultObject.get('formatted_address').to_char() );
  -- Show the json location data 
  dbms_output.put_line('Location = ' || resultObject.get('geometry').to_char() );
END IF;

Running this code will output this to the dbms output:

Status = OK
Address = "St Paul, MN 55105, USA"
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
    }
  }
}

---

Related Topics