Ping function in PL SQL
641220Sep 4 2010 — edited May 24 2012Hello,
I was trying to do a ping PL SQL function that I could use to check if a web service is up or not before querying. I found some examples and finally did the below, am getting ORA-00904 & cannot see what is wrong. Maybe someone out there could help me out?
-----
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 (p_HOST_NAME, 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;
-----
Am on Oracle 10g
Best regards,