Forum Stats

  • 3,740,417 Users
  • 2,248,254 Discussions
  • 7,861,243 Comments

Discussions

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:

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
  • 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..
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,444 Red Diamond
    DO NOT HIJACK OLD AND DEAD THREADS IN ORDER TO RAISE YOUR PROBLEM!

    Standard forum netiquette.
This discussion has been closed.