Unable to capture the transfer_timeout error in PL/SQL exception handler

Question

I am setting the timeout for 2 seconds but the code is running for a minute and is then going to 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 isn't the timeout being caught?

Just 'timeout' - no ORA number or full message? Not, say, "ORA-29260: network error: TNS:operation timed out"? — Alex Poole, Aug 19 '15 at 10:58
Add `SQLCODE` to the exception message to see the ORA-xxxxx code. — Gary_W, Aug 19 '15 at 13:52
Hello Friends, PFB the Exception captured under "When others" "Exception in others :ORA-12535: TNS:operation timed out" — StandForTheRight, Aug 19 '15 at 14:36

score 4 · Accepted Answer · answered Aug 19 '15 at 17:38

You're getting a connection timeout rather than a transfer timeout. It's raised from the BEGIN_REQUEST, not the GET_RESPONSE; it isn't getting as far as transferring anything, it's just trying to open the connection to the remote host and port. If you had a connection and a GET or POST whose request/response exceeded 2 seconds then you would see UTL_HTTP.transfer_timeout. But you're seeing a transport layer problem, not an HTTP request problem.

You can catch the 'HTTP failure' part, but not specifically the TNS timeout as that is handled 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 could dig into the exception stack when you see that and pick out more specific errors, but it isn't clear what you want to do with the specific cause. Presumably you don't really want to catch it all, you're just debugging the exception to se why the timeout you set isn't working...

I'm not aware of any way to change the length of that timeout. Since it's from TNS it looks like it should be affected by the sqlnet.outbound_connect_timeout, but you set that in sqlnet.ora, and the server doesn't seem to be taking any notice of that in this scenario. It may be using the operating system defaults.

Alex - Thanks for the above snippet. I am facing a similar issue (getting a transfer time out error, but it looks like the issue is an http_failure - after applying the above exception section). Do you have any recommendations on how I can go about digging into the exception stack to find the exact issue? — Santhosh Jose, Jun 19 '21 at 02:27

Unable to capture the transfer_timeout error in PL/SQL exception handler

1 Answers1