Closing UTL_HTTP connections

user12020272

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;

Ganesh Srivatsav

I think you need to use destroy context as well.

UTL_HTTP.DESTROY_REQUEST_CONTEXT(request_context);

this is an example that fits your requirement exactly.

http://download.oracle.com/docs/cd/E14072_01/appdev.112/e10577/u_http.htm#BHAGGAGH

G.

Ganesh Srivatsav

You can end the request

UTL_HTTP.END_REQUEST (r IN OUT NOCOPY req);

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/u_http.htm#i1025869

To close the connections for the session. May be you will not need it,

UTL_HTTP.CLOSE_PERSISTENT_CONN

See this,

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_http.htm#sthref11992

G.

user12020272

Thanks for the reply. I have the call you're referring to in the procedure at the end:

utl_http.end_response(l_resp);

Persistent connections are not enabled by default, so I'm not using that.

Any other ideas?

Actually, I just saw that you posted the end_request procedure, but according to the documentation you're supposed to use end_response for a normal call to close the connection?

Edited by: user12020272 on May 17, 2011 12:14 PM

Ganesh Srivatsav

I think you need to use destroy context as well.

UTL_HTTP.DESTROY_REQUEST_CONTEXT(request_context);

this is an example that fits your requirement exactly.

http://download.oracle.com/docs/cd/E14072_01/appdev.112/e10577/u_http.htm#BHAGGAGH

G.

user12020272

That was it! As always, great help in these forums. Thanks!

user190553

In my code I am creating a request without actually creating a context for it, I am closing the requests but I am still getting the same error(too_many_requests), Please advise how can I handle this .

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

function invoke_ws (
p_request in out nocopy t_request,
p_url in varchar2,
p_action in varchar2,
p_account_code in varchar2,
p_account_pass in varchar2,
p_wallet_account_code in varchar2,
p_wallet_account_pass in varchar2) return t_response as

l_envelope clob := null;
l_http_request utl_http.req;
l_http_response utl_http.resp;
l_response t_response;
l_errcode varchar2(10000);
l_errmsg varchar2(10000);

procedure cleanup is
--close any open handles, ignore errors here
begin
begin
if l_http_request.private_hndl is not null then
utl_http.end_request(l_http_request);
end if;
exception
when others then
null;
end;

begin
if l_http_response.private_hndl is not null then
utl_http.end_response(l_http_response);
end if;
exception
when others then
null;
end;
end;
begin

generate_envelope(
p_request,
l_envelope,
p_account_code,
p_account_pass);

--UTL_HTTP call in its own PLSQL block
begin

if (instr(p_url, 'https') > 0) then
utl_http.set_wallet(p_wallet_account_code, p_wallet_account_pass);
end if;

l_http_request := sys.utl_http.begin_request(p_url, 'POST', 'HTTP/1.0');
-- dbms_lock.sleep(29);
if g_proxy_username is not null then
utl_http.set_authentication(
r => l_http_request,
username => g_proxy_username,
password => g_proxy_password,
scheme => 'Basic',
for_proxy => true);
end if;

utl_http.set_header(l_http_request, 'Content-Type', 'text/xml');
utl_http.set_header(l_http_request, 'Content-Length', length(l_envelope));
utl_http.set_header(l_http_request, 'SOAPAction', p_action);

utl_http.write_text(l_http_request, l_envelope);

l_http_response := utl_http.get_response(l_http_request);
utl_http.read_text(l_http_response, l_envelope);
utl_http.end_response(l_http_response);

exception
when utl_http.end_of_body then
cleanup;
raise_application_error(-20000, 'End of body exception:' || utl_http.get_detailed_sqlerrm);
when utl_http.request_failed then
cleanup;
raise_application_error(-20000, 'Request failed exception:' || utl_http.get_detailed_sqlerrm);
when utl_http.http_server_error then
cleanup;
raise_application_error(-20000, 'Server exception:' || utl_http.get_detailed_sqlerrm);
when others then
cleanup;
l_errmsg := utl_http.get_detailed_sqlerrm;
if l_errmsg is null then
l_errmsg := sqlerrm;
end if;
raise_application_error(-20000, 'Unknown exception:' || l_errmsg);
end;

--cleanup any orphan handles
cleanup;

--check for soap faults
get_fault(l_response, l_errcode, l_errmsg);
if l_errcode is not null then
raise_application_error(-20000, 'Unknown SOAP error:' || l_errcode || ':' || l_errmsg);
end if;

l_response.doc := xmltype.createxml(l_envelope);
return l_response;

exception
when others then
l_errmsg := sqlerrm;
raise_application_error(-20000, 'Unknown error:' || l_errmsg);
end;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------