Support for JSON in Oracle 11G

Support for JSON in Oracle 11g

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

Regards

Is it possible to ouput my data in JSON instead of XML in Oracle 11g

I guess it depends on what you mean by "convert".

1. If you literally mean convert and are looking for a tool that takes arbitrary XML and returns JSON, well, writing that would be a lot of work. Someone may have done that already, I don't know.
2. If you just need this output in JSON, you could find wherever your XML is generated and rewrite it (I assume it's backed by one or more SQL queries) and call a PL/SQL package that generates JSON. My first stop would be plsql-utils library and JSON_UTIL_PKG.
3. Or, take the function that generates your XML and rewrite it to construct JSON via string operations. JSON is just formatted text, after all. I've done this before and it might be the quickest way if your needs are simple.

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
    }
  }
}

Using PL/JSON in oracle forms 11g

ORA-00600 is Oracle's code for "unhandled side effects" ie bugs. There should be some additional information in the alert log. The standard ORA-00600 advice is to contact Oracle Support.

If you don't have a Support contract your options are limited. The ORA-00600 message should come with one or more arguments in square brackets, like this

 ORA-00600: internal error code, arguments: [17069], [55573516], [], [], [], [], [], []  

Those arguments are useful for identifying specific causes (the first number usually indicates an Oracle error number, documented or otherwise). You can try Googling for them.

As to your specific case I'm afraid we can only speculate. The object method JSON.TO_CHAR() calls a PL/SQL package function,JSON_PRINTER. That may be the problem. Forms PL/SQL shares a syntax with database PL/SQL but they are different engines; it may be you have come across a bug in how Forms PL/SQL works with SQL Types which have dependencies.

---

Related Topics