在oracle工作与json

有没有一个简单的方法来处理oracle中的JSON？我有一个常用的调用Web服务的标准过程，JSON是我在Web开发环境中熟悉的一种格式，但在存储过程中使用json的最佳方法是什么？例如从URI获取CLOB响应，将其转换为JSON对象并从中获取值？

为了参考，这里是我用来获取URL的过程

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;

然后测试

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

(我知道googleapi将允许xml响应，但还有其他Web API，我经常使用默认的JSON)

我已经开始使用这个图书馆了，这似乎很有希望：
https://github.com/pljson/pljson

易于安装，示例不错。

要在示例中使用库，请将这些变量添加到您的过程中。

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

….

然后可以将响应作为json对象进行操作。

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

运行此代码将输出到dbms输出：

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

在oracle工作与json

猜你在找的Oracle相关文章