Failed to capture transfer_timeout error in PL / SQL exception handler

Question

I set the timeout to 2 seconds, but the code works for a minute, and then goes when others instead of when UTL_HTTP.transer_timeout .

 DECLARE request UTL_HTTP.REQ; response UTL_HTTP.RESP; n NUMBER; buff VARCHAR2 (4000); clob_buff CLOB; BEGIN UTL_HTTP.SET_RESPONSE_ERROR_CHECK (FALSE); UTL_HTTP.set_transfer_timeout (2); request := UTL_HTTP.BEGIN_REQUEST ('www.google.com:81', 'GET'); UTL_HTTP.SET_HEADER (request, 'User-Agent', 'Mozilla/4.0'); response := UTL_HTTP.GET_RESPONSE (request); DBMS_OUTPUT.PUT_LINE ( 'HTTP response status code: ' || response.status_code); EXCEPTION WHEN UTL_HTTP.transfer_timeout THEN DBMS_OUTPUT.put_line ('Timeout'); WHEN OTHERS THEN DBMS_OUTPUT.put_line ('Exception in others :' || SQLERRM); END; 

Why is the timeout not caught?

Answer 1

You get a connection timeout, not a transmission timeout. It is raised from BEGIN_REQUEST , not GET_RESPONSE ; it doesn’t get to transfer anything, it just tries to open a connection to the remote host and port. If you have a connection and a GET or POST whose request / response has exceeded 2 seconds, you will see UTL_HTTP.transfer_timeout . But you see a transport layer problem, not an HTTP request problem.

You can catch the "HTTP Failure" part, but not specifically the TNS timeout, since it is being processed by UTL_HTTP:

 DECLARE ... http_failure exception; pragma exception_init(http_failure, -29273); BEGIN ... EXCEPTION WHEN UTL_HTTP.transfer_timeout THEN DBMS_OUTPUT.put_line ('Transfer timeout'); WHEN http_failure THEN DBMS_OUTPUT.put_line ('HTTP failure - timeout?'); RAISE; END; 

You can pop into the exception stack when you see this and choose more specific errors, but it’s not clear what you want to do with the specific reason. Presumably, you really do not want to catch all this, you are just debugging the exception, because the timeout that you set does not work ...

I do not know how to change the length of this timeout. Since this is from TNS, it seems sqlnet.outbound_connect_timeout should affect sqlnet.outbound_connect_timeout , but you set it to sqlnet.ora and the server does not seem to notice this in this scenario. It can use the default operating system settings.