1

I'm using PL/JSON library to parse data from MongoDB to Oracle DB. I use UTL_HTTP package as follows:

  l_http_request := UTL_HTTP.begin_request('https://api.appery.io/rest/1/db/collections/Outlets'
                                          , 'GET'
                                          , 'HTTP/1.1');

  -- ...set header's attributes
  UTL_HTTP.set_header(l_http_request, 'X-Appery-Database-Id', '53f2dac5e4b');
  --UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_param_list));
  -- ...set input parameters

  -- get Response and obtain received value
  l_http_response := UTL_HTTP.get_response(l_http_request);

--using a loop read the response.
    BEGIN
      LOOP
        UTL_HTTP.read_text(l_http_response, buf);
        l_response_text := l_response_text || buf;
      END LOOP;
    EXCEPTION
    WHEN UTL_HTTP.end_of_body THEN
      NULL;
    END;
  l_list := json_list(l_response_text);


FOR i IN 1..l_list.count
LOOP
  A_id           := json_ext.get_string(json(l_list.get(i)),'_id');
.....

The loop extracts and inserts records. However, the number of records inserted does not exceed 100 records even though the data requested is much more than that.

I tried with different JSON collections, and different Oracle tables but the result is same. The maximum number of records I get is 100 records.

Is their any attributes I need to add to my response?

Neil Lunn
  • 148,042
  • 36
  • 346
  • 317
Hawk
  • 5,060
  • 12
  • 49
  • 74
  • Are you sure your `l_response_text` contains more than 100 records? Also, have you tried simply parsing it as just JSON, e.g. `l_json := json(l_response_text)`? – James Sumners Jan 20 '15 at 20:25
  • Thinking about it some more, I'm pretty sure your `l_response_text` is a `varchar2`. That data type can only hold ~32KB of data. If your >100 records has exceeded that amount, then your data is definitely going to be truncated. – James Sumners Jan 20 '15 at 21:22

0 Answers0