Http Connection Checking Mechanism With Sql

I found a java code as showns below ;


public void testURL() throws Exception {
    String strUrl = "http://stackoverflow.com/about";

    try {
        URL url = new URL(strUrl);
        HttpURLConnection urlConn = (HttpURLConnection) url.openConnection();
        urlConn.connect();

        assertEquals(HttpURLConnection.HTTP_OK, urlConn.getResponseCode());
    } catch (IOException e) {
        System.err.println("Error creating HTTP connection");
        e.printStackTrace();
        throw e;
    }
}




How can I use this in pl/sql  ?

Or can we use  utl_http  package ?

if your utl_http session was using persistent connections  you can use

utl_http.get_persistent_conn_count  to see how many there are still open


utl_http.get_persistent_conns to return all of them and iterate through them as needed, checking the underlying utl_tcp connections

if you're not using persisent connections then you're not connected when you're between calls.


if you're trying to use utl_http to see if some one else has a connection open to the server then that won't work.  utl_http only knows what your own session is doing.

Then what will be the command to check  the connectivity of a web page for instance www.google.com  in sql ?

what do you mean by "check the connectivity"  ?

If you're simply trying to see if it's possible to connect then you don't need utl_http, just utl_tcp is sufficient

CREATE OR REPLACE FUNCTION can_connect(p_server IN VARCHAR2, p_port IN INTEGER DEFAULT 80)
    RETURN VARCHAR2
IS
    v_conn UTL_TCP.connection;
BEGIN
    v_conn := UTL_TCP.open_connection(remote_host => p_server, remote_port => p_port);
    UTL_TCP.close_connection(v_conn);
    RETURN 'YES';
EXCEPTION
    WHEN OTHERS
    THEN
        -- do something with the error message so we know why we can't connect
        DBMS_OUTPUT.put_line(SQLERRM);
        RETURN 'NO';
END;

Select allOpen in new window

by "check the connectivity"    I' d like to test if I can connect to a  http web page  like http:\\www.google.com .

I think your last comment checks   if there exists a connection to a server.

Is there any method to check if I can connect to a web page from sql  ?

no, the "can_connect" function checks if you can connect.

as stated previously you can't determine if there is already a connection via sql unless your current session is one of those connections.

In your can_connect  function  can you check an http  link ?    

That is  p_server =  http:\\www.xxxx.com

remove the http portion, that's what the port is for and it defaults to port 80 (http)

SQL> set serveroutput on
SQL> select can_connect('google.com') from dual;

CAN_CONNECT('GOOGLE.COM')
--------------------------------------------------------------------------------
YES

SQL> select can_connect('does_not_exist.com') from dual;

CAN_CONNECT('DOES_NOT_EXIST.COM')
--------------------------------------------------------------------------------
NO

ORA-29260: network error: Connect failed because target host or object does not
exist

Select allOpen in new window

In fact the related  http link is a webservice link like

http://xxx.com/entegrasyonservis/entegrasyonservis.asmx

Thanks  Sdstuber,

I created the function successfully . And I wrote query as  

'select check_url('http://www.google.com') from dual;'     on the sql prompt  and there

returned the value 'NO'.  But I can connect to www.google.com  from internet explorer

successfully.    What can be the reason for returning the value 'NO' ?

look at the dbms output  it'll tell you the error just like in my examples above.


just a guess though.  If you're on 11g it's probably ACLs.


https://www.experts-exchange.com/Database/Oracle/A_8429-How-to-use-Access-Control-Lists-in-Oracle.html

https://www.experts-exchange.com/Database/Oracle/A_9074-How-to-use-Network-Access-Control-Lists-in-Oracle.html