ORA-29270: too many open HTTP requests

Question

Can someone help me with this problem that occurs whenever you run a TRIGGER, but works in a normal PROCEDURE?

TRIGGER:

create or replace
procedure testeHTTP(search varchar2)
      IS

Declare
     req   sys.utl_http.req;<BR>
  resp  sys.utl_http.resp;<BR>
 url varchar2(500);

Begin


  url := 'http://www.google.com.br';

  dbms_output.put_line('abrindo');
  -- Abrindo a conexão e iniciando uma requisição
  req := sys.utl_http.begin_request(search);

  dbms_output.put_line('preparando');
  -- Preparandose para obter as respostas
  resp := sys.utl_http.get_response(req);


 dbms_output.put_line('finalizando response');
  -- Encerrando a comunicação request/response
  sys.utl_http.end_response(resp);


Exception
  When Others Then
    dbms_output.put_line('excecao');
    dbms_output.put_line(sys.utl_http.GET_DETAILED_SQLERRM());

End;

Cucullate

Answer 1

close your user session and then the problem is fixed.

Internal there is a limit from 5 http requests.

Might a problem is the missing: utl_http.end_response

or an exception in the app and not a close from the resp object.

modify the code like that:

EXCEPTION
  WHEN UTL_HTTP.TOO_MANY_REQUESTS THEN
  UTL_HTTP.END_RESPONSE(resp); 

Wits

I changed my implementantion following utl_http oracle doc reference but the error was persisting. Close my user session was what worked. – Sanalda

Answer 2

you need to close your requests once you are done with them, it does not happen automatically (unless you disconnect form the db entirely)

It used to be utl_http.end_response, but I am not sure if it is the same api any more.

Visakhapatnam

Answer 3

Usually we need UTL_HTTP.END_RESPONSE(resp); to avoid of ORA-29270: too many open HTTP requests, but I think I reproduced the problem of @Clóvis Santos in Oracle 19c.

If web-service always returns status 200 (success) then too many open HTTP requests never happens. But if persistent connections are enabled and web-service returns status 404, behavior becomes different.

Let`s call something that always return 404.

First call of utl_http.begin_request returns normally and opens new persistent connection. We can check it with select utl_http.get_persistent_conn_count() from dual;. Second call causes an exception inside utl_http.begin_request and persistent connection becomes closed. (Exception is correctly handled with end_response/end_request).

If I continue then each odd execution returns 404 normally and each even execution gives an exception (handled correctly of course).

After some iterations I get ORA-29270: too many open HTTP requests. If web-service returns status 200 everything goes normally.

I guess, it happens because of the specific web-service. Probable it drops persistent connection after 404 and doesn't after 200. Second call tries to reuse request on persistent connection but it doesn't exist and causes request leak.

If I use utl_http.set_persistent_conn_support (false, 0); once in my session the problem disappears. I can call web-service as many times as I need.

Resolution: Try to switch off persistent connection support. Probable, on the http-server persistent connections work differently for different requests. Looks like a bug.

Discobolus