Forum Stats

  • 3,872,311 Users
  • 2,266,414 Discussions
  • 7,911,141 Comments

Discussions

Closing UTL_HTTP connections

user12020272
user12020272 Member Posts: 90
edited May 24, 2011 12:51AM in SQL & PL/SQL
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;

Best Answer

  • Ganesh Srivatsav
    Ganesh Srivatsav Member Posts: 2,284 Silver Trophy
    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.

Answers

  • Ganesh Srivatsav
    Ganesh Srivatsav Member Posts: 2,284 Silver Trophy
    edited May 17, 2011 10:35AM
    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.
    Ganesh Srivatsav
  • user12020272
    user12020272 Member Posts: 90
    edited May 17, 2011 12:17PM
    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
    Ganesh Srivatsav Member Posts: 2,284 Silver Trophy
    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.
  • That was it! As always, great help in these forums. Thanks!
  • 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;
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This discussion has been closed.