Link to home
Start Free TrialLog in
Avatar of cavtel
cavtel

asked on

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

DECLARE
  c  utl_tcp.connection;  -- TCP/IP connection to the Web server
  ret_val pls_integer;
BEGIN
  c := utl_tcp.open_connection(remote_host => '8.8.8.8',
                               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);
  BEGIN
    LOOP
      dbms_output.put_line(utl_tcp.get_line(c, TRUE));  -- read result
    END LOOP;
  EXCEPTION
    WHEN utl_tcp.end_of_input THEN
      NULL; -- end of input
  END;
  utl_tcp.close_connection(c);
END;
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

There appears to be a timeout parameter for open_connection:

http://psoug.org/reference/utl_tcp.html
From the doc:

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/u_tcp.htm#i997069

You should be able to supply the TX_TIMEOUT parameter to the OPEN_CONNECTION function.
Avatar of cavtel

ASKER

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.
UTL_TCP might be governed by the sqlnet parameters.  You might tweak these to see if they make a difference:

http://download.oracle.com/docs/cd/E11882_01/network.112/e10835/tnsnames.htm#NETRF1434
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial