title: Parse JSON into Oracle table using PL/SQL
body: <p>I have created the following script in order to read data from Mobile App DB (which is based on MongoDB) from Oracle SQL Developer:</p>
  
  <pre>
  DECLARE
    l_param_list     VARCHAR2(512);
  
    l_http_request   UTL_HTTP.req;
    l_http_response  UTL_HTTP.resp;
  
    l_response_text  VARCHAR2(32767);
  BEGIN
  
    -- service's input parameters
  
    -- preparing Request...
    l_http_request := UTL_HTTP.begin_request ('https://api.appery.io/rest/1/db/collections/Photos?where=%7B%22Oracle_Flag%22%3A%22Y%22%7D'
                                            , 'GET'
                                            , 'HTTP/1.1');
  
    -- ...set header's attributes
    UTL_HTTP.set_header(l_http_request, 'X-Appery-Database-Id', '53f2dac5e4b02cca64021dbe');
    --UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_param_list));
  
    -- ...set input parameters
   -- UTL_HTTP.write_text(l_http_request, l_param_list);
  
    -- get Response and obtain received value
    l_http_response := UTL_HTTP.get_response(l_http_request);
  
    UTL_HTTP.read_text(l_http_response, l_response_text);
  
    DBMS_OUTPUT.put_line(l_response_text);
    insert into appery values(l_response_text);
    -- finalizing
    UTL_HTTP.end_response(l_http_response);
  
  
  EXCEPTION
    WHEN UTL_HTTP.end_of_body 
      THEN UTL_HTTP.end_response(l_http_response);  
  END;
  /</pre>  
  
  <p>The response <em>(l_response_text)</em> is a JSON-like string. For example:</p>
  
  <pre>[{"Postcode":"47100","OutletCode":"128039251","MobileNumber":"0123071303","_createdAt":"2014-11-10 06:12:49.837","_updatedAt":"2014-11-10 06:12:49.837"}, {"Postcode":"32100","OutletCode":"118034251", ..... ]</pre>
  
  <p>The code works fine, and inserts the response into one column-table called <em>appery</em>. However, I need to parse this response such that each array goes into into its specific column in a table called <em>appery_test</em>. The table <em>appery_test</em> has a number of columns same as the number of JSON pairs and in the same order.</p>
  
  <p>I searched and I found most of the results about parsing Oracle table into JSON and not the opposite. I found, though, <a href="https://stackoverflow.com/questions/7985841/work-with-json-in-oracle">this</a> link which is somewhat similar to my issue. However, the suggested library in the answer does not have any example on how to use it to insert JSON into conventional table using PL/SQL. </p>
  
  <p><strong>N.B.:</strong> I'm using 11g and not 12c. So the <a href="https://dba.stackexchange.com/questions/7535/work-with-json-in-oracle">built in</a> functions are not available for me.</p>