Goal:

Tracking of user activities by getting his/her IP is useful. However, it would be great if we find the location where he/she is coming from.

 

To get the IP of the client you could use this PL/SQL code

OWA_UTIL.get_cgi_env('X-Forwarded-For')

 

The Geolocation is not offered by any Oracle PL/SQL function. That's why we could use a nice free RESTful API to catch it. I wrote a function for that

create or replace FUNCTION  "GEOLOCATION" (p_ip in VARCHAR2) return CLOB is
    base_url varchar2(150);
    l_clob CLOB;
begin
    base_url := 'http://ip-api.com/json/' || p_ip;    
    
    /* RABIE: GET Request */
    l_clob := APEX_WEB_SERVICE.make_rest_request(
        p_url         => base_url,    
        p_http_method => 'GET'
    );
      
    /*RABIE: return value (TODO: should return the result from the server)*/
    return l_clob;
    
end;

 

I hope that helps someone

 

Regards

Mahmoud