Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

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;
This post has been answered by Ganesh Srivatsav on May 17 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 21 2011
Added on May 17 2011
5 comments
17,519 views