Thursday, January 21, 2010

use Ping utility from plsql function

CREATE OR REPLACE FUNCTION ping (
p_host_name VARCHAR2,
p_port NUMBER DEFAULT 1000
)
RETURN VARCHAR2
IS
tcpconnection UTL_TCP.connection;
c_ping_ok CONSTANT VARCHAR2 (10) := 'OK';
c_ping_error CONSTANT VARCHAR2 (10) := 'ERROR';
BEGIN
tcpconnection :=
UTL_TCP.open_connection (remote_host => p_host_name,
remote_port => p_port
);
UTL_TCP.close_connection (tcpconnection);
RETURN c_ping_ok;
EXCEPTION
WHEN UTL_TCP.network_error
THEN
IF (UPPER (SQLERRM) LIKE '%HOST%')
THEN
RETURN c_ping_error;
ELSIF (UPPER (SQLERRM) LIKE '%LISTENER%')
THEN
RETURN c_ping_ok;
ELSE
RAISE;
END IF;
END ping;




SELECT ping ('10.11.1.254', 1000)
FROM DUAL