ORA-29273: HTTP request failed intermittent error using the utl_http package

Question

I'm using the utl_http package to make HTTP GET requests to an IIS site on the same server (local) as Oracle. Sometimes it works and I get the response, but more often than not it hangs for about 15 seconds and then I get this error:

ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1722 ORA-29263: HTTP protocol error

As a test, I've got a small static text file in the IIS site, so this is how I'm testing it:

select utl_http.request('http://domain.com/test.txt') from dual

I get the same problem if I run it in Oracle Apex instead of direct on the db.

The other thing I've tried is to create a package of my own that does the HTTP request using the long utl_htp.begin_request() method, instead of the utl_http.request() shortcut. This gives the exact same problem (works sometimes but errors mostly - same error).

The pattern I'm seeing is if I wait a while and then try, it works for the first 2-10 times, and then begins erroring. When it does work, I get the response instantly and when it errors, there is always the delay before the error.

If I request the text file URL (or any other resource in the site) using a remote web browser then I get the correct response every time.

I have tried setting a timeout like below but it doesn't have any effect. For example instead of timing out after 3 seconds it continues for 10 or 15 seconds before the error is shown.

UTL_HTTP.set_transfer_timeout(3);

I think I can rule out ACL because it works sometimes.

Does anyone know what might cause this behaviour?

Solution

Possible reasons

-> You may have a problem with your TNS-Listener.

From the command prompt window, try to run TNSPING service_name .. try to run it quickly several times and check if it fails in some of them.

I had once a similar problem. Try to re-configure your TNS-Listener.

There must be also an option in which you can give an IP number in the TNS-listener definition. This also solves sometimes these kind of problems.

-> IIS problem.