work with json in oracle

Question

Is there an easy way to work with JSON within oracle? I have a standard procedure that I use to call web services quite often, JSON is a format that I am familiar with in web development context, but what is the best way to work with json within a stored procedure? For instance take the CLOB response from the URI, convert that to a JSON object and get a value from that?

For reference sake, here is the procedure I used to fetch URLs

create or replace procedure macp_URL_GET(url_resp in out clob, v_url in varchar2) is
   req     Utl_Http.req;
   resp    Utl_Http.resp;
   NAME    VARCHAR2 (255);
   VALUE   VARCHAR2 (1023);
   v_msg   VARCHAR2 (80);
   v_ans clob;
--   v_url   VARCHAR2 (32767) := 'http://www.macalester.edu/';
BEGIN
   /* request that exceptions are raised for error Status Codes */
   Utl_Http.set_response_error_check (ENABLE => TRUE );
   /* allow testing for exceptions like Utl_Http.Http_Server_Error */
   Utl_Http.set_detailed_excp_support (ENABLE => TRUE );
   /*
   Utl_Http.set_proxy (
      proxy                 => 'www-proxy.us.oracle.com',
      no_proxy_domains      => 'us.oracle.com'
   );
   */
   req := Utl_Http.begin_request (url => v_url, method => 'GET');
   /*
    Alternatively use method => 'POST' and Utl_Http.Write_Text to
    build an arbitrarily long message
  */

  /*
   Utl_Http.set_authentication (
      r              => req,
      username       => 'SomeUser',
      PASSWORD       => 'SomePassword',
      scheme         => 'Basic',
      for_proxy      => FALSE      --this info is for the target Web server 
   );
   */

   Utl_Http.set_header (r => req, NAME => 'User-Agent', VALUE => 'Mozilla/4.0');
   resp := Utl_Http.get_response (r => req);
   /*
   DBMS_OUTPUT.put_line ('Status code: ' || resp.status_code);
   DBMS_OUTPUT.put_line ('Reason phrase: ' || resp.reason_phrase);
   FOR i IN 1 .. Utl_Http.get_header_count (r => resp)
   LOOP
      Utl_Http.get_header (r => resp, n => i, NAME => NAME, VALUE => VALUE);
      DBMS_OUTPUT.put_line (NAME || ': ' || VALUE);
   END LOOP;
   */
--test
   BEGIN
      LOOP
         Utl_Http.read_text (r => resp, DATA => v_msg);
         --DBMS_OUTPUT.put_line (v_msg);
         v_ans := v_ans || v_msg;
         url_resp := url_resp || v_msg;
      END LOOP;
   EXCEPTION
      WHEN Utl_Http.end_of_body
      THEN
         NULL;
   END;
--test
   Utl_Http.end_response (r => resp);


   --url_resp := v_ans;

EXCEPTION
   /*
    The exception handling illustrates the use of "pragma-ed" exceptions
    like Utl_Http.Http_Client_Error. In a realistic example, the program
    would use these when it coded explicit recovery actions.

    Request_Failed is raised for all exceptions after calling
    Utl_Http.Set_Detailed_Excp_Support ( ENABLE=>FALSE )
    And it is NEVER raised after calling with ENABLE=>TRUE
  */
   WHEN Utl_Http.request_failed
   THEN
      DBMS_OUTPUT.put_line (
         'Request_Failed: ' || Utl_Http.get_detailed_sqlerrm
      );
      url_resp :='Request_Failed: ' || Utl_Http.get_detailed_sqlerrm;
   /* raised by URL http://xxx.oracle.com/ */
   WHEN Utl_Http.http_server_error
   THEN
      DBMS_OUTPUT.put_line (
         'Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm
      );
      url_resp := 'Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm;
   /* raised by URL http://otn.oracle.com/xxx */
   WHEN Utl_Http.http_client_error
   THEN
      DBMS_OUTPUT.put_line (
         'Http_Client_Error: ' || Utl_Http.get_detailed_sqlerrm
      );
      url_resp := 'Http_Client_Error: ' || Utl_Http.get_detailed_sqlerrm;
   /* code for all the other defined exceptions you can recover from */
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
      url_resp := SQLERRM;
END;

Then to test it

begin
  macp_url_get(url_resp => :url_resp,
               'http://maps.googleapis.com/maps/api/geocode/json?address=55105&sensor=false');
end;

(I know that the googleapi will allow xml response, but there are other web APIs that I use regularly that default to JSON)

Answer 1

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