Skip to Main Content

Closing UTL_HTTP connections

user12020272May 17 2011 — edited May 24 2011
Hello,
11gr2 database. I'm wondering why I'm still running into the "ORA-29270: too many open HTTP requests" error after five requests. I've used all the suggests from this forum to try and fix this. It works fine until I run it five times, which I know is the limit of open http connections. What am I not understanding/missing? Maybe I'm missing a concept? Thanks for all the help, gurus!
I'm just calling the below code from a session in Oracle SQL developer with:
declare
r clob;
begin
http_pkg.http_get('https://myurl',r);
dbms_output.put_line(r);
end;
Procedure to make requests:
procedure http_get(p_url varchar2, r_data OUT clob)
as
  l_req_context utl_http.request_context_key;
  l_req utl_http.req;
  l_resp utl_http.resp;
  l_buffer clob;
  l_end_loop boolean := false;
begin
  http_setup;
  l_req_context := utl_http.create_request_context(
    wallet_path => c_wallet,
    wallet_password => null);
  l_req := utl_http.begin_request(
    url => p_url,
    request_context => l_req_context);
  utl_http.set_header(l_req, 'User-Agent', c_user_agent);
  l_resp := utl_http.get_response(l_req);
  loop
  exit when l_end_loop;
    begin
      utl_http.read_line(l_resp, l_buffer, true);
      if(l_buffer is not null and (length(l_buffer)>0)) then
        r_data := r_data||l_buffer;
      end if;
    exception
    when utl_http.end_of_body then
      l_end_loop := true;
    end;
  end loop;

  utl_http.end_response(l_resp);
exception
when utl_http.end_of_body then
  utl_http.end_response(l_resp);
when utl_http.too_many_requests then
   utl_http.end_response(l_resp);
when others then
  utl_http.end_response(l_resp);
  raise_application_error(-20001, 'UNHANDLED_EXCEPTION ' || sqlerrm);
end http_get;
Comments
Post Details
Added on May 17 2011
5 comments
13,939 views