title: work with json in oracle
body: <p>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?</p>
  
  <p>For reference sake, here is the procedure I used to fetch URLs</p>
  
  <pre><code>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 =&gt; TRUE );
     /* allow testing for exceptions like Utl_Http.Http_Server_Error */
     Utl_Http.set_detailed_excp_support (ENABLE =&gt; TRUE );
     /*
     Utl_Http.set_proxy (
        proxy                 =&gt; 'www-proxy.us.oracle.com',
        no_proxy_domains      =&gt; 'us.oracle.com'
     );
     */
     req := Utl_Http.begin_request (url =&gt; v_url, method =&gt; 'GET');
     /*
      Alternatively use method =&gt; 'POST' and Utl_Http.Write_Text to
      build an arbitrarily long message
    */
  
    /*
     Utl_Http.set_authentication (
        r              =&gt; req,
        username       =&gt; 'SomeUser',
        PASSWORD       =&gt; 'SomePassword',
        scheme         =&gt; 'Basic',
        for_proxy      =&gt; FALSE      --this info is for the target Web server 
     );
     */
  
     Utl_Http.set_header (r =&gt; req, NAME =&gt; 'User-Agent', VALUE =&gt; 'Mozilla/4.0');
     resp := Utl_Http.get_response (r =&gt; 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 =&gt; resp)
     LOOP
        Utl_Http.get_header (r =&gt; resp, n =&gt; i, NAME =&gt; NAME, VALUE =&gt; VALUE);
        DBMS_OUTPUT.put_line (NAME || ': ' || VALUE);
     END LOOP;
     */
  --test
     BEGIN
        LOOP
           Utl_Http.read_text (r =&gt; resp, DATA =&gt; 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 =&gt; 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=&gt;FALSE )
      And it is NEVER raised after calling with ENABLE=&gt;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;
  </code></pre>
  
  <p>Then to test it </p>
  
  <pre><code>begin
    macp_url_get(url_resp =&gt; :url_resp,
                 'http://maps.googleapis.com/maps/api/geocode/json?address=55105&amp;sensor=false');
  end;
  </code></pre>
  
  <p>(I know that the googleapi will allow xml response, but there are other web APIs that I use regularly that default to JSON)</p>