how to set a connection timeout for ORACLE PLSQL UTL_TCP.open_connection

anybody know how to set a connection timeout?  if the host is down the open_connection takes 3 or 4 minutes of hanging and then an exception.  A host being down can happen and it would be good to set say a 10 second timeout.

TNS:operation timed out
ORA-29260: network error: TNS:operation timed out

  c  utl_tcp.connection;  -- TCP/IP connection to the Web server
  ret_val pls_integer;
  c := utl_tcp.open_connection(remote_host => '',
                               remote_port =>  9330,
                               charset     => 'US7ASCII');  -- open connection
  ret_val := utl_tcp.write_line(c, 'GET /axis/services/NSRAPIService HTTP/1.0');    -- send HTTP request
  ret_val := utl_tcp.write_line(c);
      dbms_output.put_line(utl_tcp.get_line(c, TRUE));  -- read result
    WHEN utl_tcp.end_of_input THEN
      NULL; -- end of input
Who is Participating?
AnandConnect With a Mentor Commented:
in database server's listener.ora, there are two parameters.
inbound_connection_timeout parameter, and outbout_connection_timeout parameters.
For example, if the value is 60 and the connection is not made successful within 60 seconds, then timeout happens. With your requirement, you can try these values by setting 10.i.e, 10 seconds.
For exact parameter name, u can google. Listener restart is required when a change happens in listener.ora to take effect.
slightwv (䄆 Netminder) Commented:
There appears to be a timeout parameter for open_connection:
johnsoneSenior Oracle DBACommented:
From the doc:

You should be able to supply the TX_TIMEOUT parameter to the OPEN_CONNECTION function.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

cavtelAuthor Commented:
unfortunately that tx_timeout is transfer_timeout used for read and write, after the connection is made.

the utl_http function works the same way, you can set a request timeout, but not on the initial connection.

the connection will hang for 3 minutes, i was trying to figure out an alter session parameter to shorten that 3 minutes as a work around.  but couldnt figure that out either.
slightwv (䄆 Netminder) Commented:
UTL_TCP might be governed by the sqlnet parameters.  You might tweak these to see if they make a difference:
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.