Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there an easy way to work with JSON within Oracle? I have a stored 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/';
   /* 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)
      Utl_Http.get_header (r => resp, n => i, NAME => NAME, VALUE => VALUE);
      DBMS_OUTPUT.put_line (NAME || ': ' || VALUE);
         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;
      WHEN Utl_Http.end_of_body
   Utl_Http.end_response (r => resp);

   --url_resp := v_ans;

    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
      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
      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
      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 */
      DBMS_OUTPUT.put_line (SQLERRM);
      url_resp := SQLERRM;

Then to test it

  macp_url_get(url_resp => :url_resp,

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

share|improve this question
What version of Oracle? 12c introduces native JSON types. –  Justin Cave Oct 13 '14 at 19:58

2 Answers 2

If you have Oracle Database Release 12c Release 1 ( then you can store JSON in the database in a VARCHAR2, CLOB, or BLOB column, and then query it in SQL using JSON path expressions (analogous to XPath expressions for XML).

You can use SQL functions json_value, json_query, and json_table, and SQL conditions json_exists, is json, is not json, and json_textcontains, to query the data.

Functions json_query and json_table are analogous to SQL/XML standard functions XMLQuery and XMLTable for XML. Condition json_exists is analogous to XMLExists for XML.

You ensure that the data in a JSON column is in fact valid JSON data by defining an is json check constraint on it. You can define JSON-specific indexes on the data.

See JSON in Oracle Database.

(Affiliation: I work at Oracle. The views I express on this website are my own and do not necessarily reflect the views of Oracle.)

share|improve this answer
Do I need to enable any libraries, or are these functions available to all? –  Lloyd Oct 13 '14 at 3:34
Available to all. These are SQL functions that are part of the database. –  Drew Oct 13 '14 at 4:29
@Lloyd: you need, the initial 12c release which was did not contain support for JSON –  a_horse_with_no_name Oct 13 '14 at 7:48
@a_horse_with_no_name: Right. As I said in the first sentence of my post: If you have... ;-) –  Drew Oct 13 '14 at 14:01
@drew: I know. This was intended to make Lloyd aware of the subtle difference between .0.2 and .0.1 –  a_horse_with_no_name Oct 13 '14 at 14:04

Take a look at PL/JSON project https://github.com/pljson/pljson

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.