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(re mote_host => '8.8.8.8',
remote_port => 9330,
charset => 'US7ASCII'); -- open connection
ret_val := utl_tcp.write_line(c, 'GET /axis/services/NSRAPIServi ce HTTP/1.0'); -- send HTTP request
ret_val := utl_tcp.write_line(c);
BEGIN
LOOP
dbms_output.put_line(utl_t cp.get_lin e(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;
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(re
remote_port => 9330,
charset => 'US7ASCII'); -- open connection
ret_val := utl_tcp.write_line(c, 'GET /axis/services/NSRAPIServi
ret_val := utl_tcp.write_line(c);
BEGIN
LOOP
dbms_output.put_line(utl_t
END LOOP;
EXCEPTION
WHEN utl_tcp.end_of_input THEN
NULL; -- end of input
END;
utl_tcp.close_connection(c
END;
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.
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.
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.
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
http://download.oracle.com/docs/cd/E11882_01/network.112/e10835/tnsnames.htm#NETRF1434
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://psoug.org/reference/utl_tcp.html