Forum Stats

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

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Log In Register

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,444 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.