ORA-29270 Too Many Open HTTP Requests

Today one of the developer got the below error, i thought to write the details for him.



Reason:
The database server has a hardcoded limit of 5 open HTTP connections per session. When you attempt to open a 6th http connection, this error is thrown. There is no way around this so if you actually are trying to access more than 5 HTTP connections at a time, you will have to redesign the application within this limitation. Most of the time, however, the application is really intending to open one connection at a time but somehow this error is still thrown. This means that the application is not closing the connection properly (ie. not calling utl_http.end_response in the proper scope).

If the application is actually calling utl_http.end_response, then it probably is not within the proper scope. eg. the end response is not in the same routine that created the request.  

How to Handle:
It can be handled by the exception TOO_MANY_REQUESTS in plsql code like this

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

Don't call UTL_HTTP.END_RESPONSE after the RETURN call, because this will go around the UTL_HTTP connection closure.
Don't use a RETURN call to leave a loop inside a function.
When ORA-29270 occurs together with ORA-29273 like this:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1369
ORA-29270: too many open HTTP requests
ORA-06512: at line 14
then you need to handle ORA-29273 instead ORA-29270:
EXCEPTION
   WHEN UTL_HTTP.REQUEST_FAILED THEN
    UTL_HTTP.END_RESPONSE(resp);

 
Ref: 961468.1