Forum Stats

  • 3,855,400 Users
  • 2,264,500 Discussions
  • 7,905,983 Comments

Discussions

Categories

Ping function in PL SQL

641220
641220 Member Posts: 17
edited May 24, 2012 4:33AM in SQL & PL/SQL
Hello,

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,
Tagged:
· Share on TwitterShare on Facebook

Answers

  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    edited Sep 4, 2010 7:40PM 
    SQL> CREATE OR REPLACE FUNCTION ping (pHostName VARCHAR2, pPort NUMBER DEFAULT 1000) RETURN VARCHAR2
 AUTHID DEFINER IS
  2   tcpCnx utl_tcp.connection;
  3   cOk    CONSTANT VARCHAR2(2) := 'OK';
  4   cFail  CONSTANT VARCHAR2(5) := 'ERROR';
  5  BEGIN
  6    tcpCnx := utl_tcp.open_connection (pHostName, pPort);
  7    utl_tcp.close_connection(tcpCnx);
  8    RETURN cOk;
  9  EXCEPTION
 10    WHEN utl_tcp.network_error THEN
 11      IF (UPPER(SQLERRM) LIKE '%HOST%') THEN
 12        RETURN cFail;
 13      ELSIF (UPPER(SQLERRM) LIKE '%LISTENER%') THEN
 14        RETURN cOk;
 15      ELSE
 16        RAISE;
 17      END IF;
 18    WHEN OTHERS THEN
 19      RAISE;
 20  END ping;
 21  /

Function created.

SQL> select ping('USPS997LT.USA.MORGANSLIBRARY.COM', 1521) from dual;

PING('USPS997LT.USA.MORGANSLIBRARY.COM',1521)
----------------------------------------------------------------------------------------------------
OK
    · Share on TwitterShare on Facebook
  • 641220
    641220 Member Posts: 17
    Thanks
    · Share on TwitterShare on Facebook
  • PeterValencic
    PeterValencic Member Posts: 522 Red Ribbon
    This function works well if the host is reachable but if the remote host is down the funtion will wait for the timeout..
    I tryed to modify your function with
    tcpConnection := UTL_TCP.open_connection(remote_host => p_host, remote_port => p_port, tx_timeout=> 2); --wait 2 secconds for the timeout..
    but it's not working.. (I don't get timeout after 2 seconds)

    What I need is to ping a webservice and get back info if the webservice host is reachable.. I need this info in less than 5 secconds...

    thank you for any help..
    · Share on TwitterShare on Facebook
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,890 Red Diamond
    DO NOT HIJACK OLD AND DEAD THREADS IN ORDER TO RAISE YOUR PROBLEM!

    Standard forum netiquette.
    · Share on TwitterShare on Facebook
This discussion has been closed.