Introduction

Sometimes I see questions how to obtain an IP address for EBS logins. Before EBS 12.2.10 it wasn’t an easy task. But starting from EBS 12.2.10 a new column “clientip” was added to fnd_logins table. So getting this information is very easy now.

Many existing and potential customers connect to our EBS Demo instance to evaluate Blitz Report. And we’re interested in how they’re spread across the world. And an IP address is not very informative in this scenario as it is required to check manually each IP address on the web to get a country and a city. So I decided to automate this task. Wouldn’t it be wonderful to have a PL/SQL function accepting an IP address and returning a client’s city, country? Let’s create such a function!

Setup

Choosing a geolocation API

First of all we need an API for returning geolocation information basing on an IP address. I chose freegeoip.app for that as it provides a nice free API.

Configuring http requests from Oracle Database

Several things need to be setup before you will be able to connect to a website from Oracle Database.

Create a new Access Control Entry (ACE)

Provide host, lower and upper ports parameters.

sqlplus '/as sysdba'
begin
  dbms_network_acl_admin.append_host_ace (
    host       => 'freegeoip.app', 
    lower_port => 80,
    upper_port => 80,
    ace        => xs$ace_type(privilege_list => xs$name_list('http'),
                              principal_name => 'apps',
                              principal_type => xs_acl.ptype_db)); 
end;
/

Check that the ACE has been created.

select host,
       lower_port,
       upper_port,
       acl,
       aclid,
       acl_owner
from   dba_host_acls
order by host;

Obain the geolocation API website certificate

Please follow the steps on the following link to obtain freegeoip.app site root certificate using your browser.

Import the certificate into an Oracle Wallet

Use the same link to create a new Oracle Wallet and import the certificate into it. Or if you have an existing wallet you can import the certificate into it.

Here’s an example of how I added the certificate and the output of orapki command displaying the contents of the wallet.

orapki wallet add -wallet /u01/install/APPS/19c/admin/CDBVIS/wallet -cert freegeoip.cer -trusted_cert -pwd *****
orapki wallet display -wallet /u01/install/APPS/19c/admin/CDBVIS/wallet
 
Oracle PKI Tool Release 21.0.0.0.0 - Production
Version 21.0.0.0.0
Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved.
 
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        CN=GlobalSign,O=GlobalSign,OU=GlobalSign Root CA - R2
Subject:        CN=Baltimore CyberTrust Root,OU=CyberTrust,O=Baltimore,C=IE

Create a geolocation function

Here I provide the code for xxen_geolocation function along with comments

create or replace function xxen_geolocation(p_ip_address in varchar2, p_division in varchar2) return varchar2 result_cache is
  l_url varchar2(50):='https://freegeoip.app/xml/'||p_ip_address;--Constructing an API url using input IP address
  l_wallet_path varchar2(50):='/u01/install/APPS/19c/admin/CDBVIS/wallet';--Setting a path for a wallet containing geolocation API site root certificate
  l_http_request utl_http.req;
  l_http_response utl_http.resp;
  l_text varchar2(32767);
  type l_geolocation_t is table of varchar2(50) index by varchar2(50);
  l_geolocation l_geolocation_t;
begin
 
  -- If using HTTPS, open a wallet containing the trusted root certificate.
  utl_http.set_wallet('file:' || l_wallet_path);
 
  -- Make a HTTP request and get the response.
  l_http_request:=utl_http.begin_request(l_url);
  l_http_response:=utl_http.get_response(l_http_request);
 
  -- Loop through the response and get the geolocation information
  loop
    utl_http.read_text(l_http_response, l_text, 32766);
    for c in 
    (
    select * from
    (select l_text from dual) xd,
    xmltable('/Response' passing xmltype(xd.l_text) columns
    country_code varchar2(10) path 'CountryCode',
    country varchar2(50) path 'CountryName',
    region varchar2(50) path 'RegionName',
    city varchar2(50) path 'City',
    zip varchar2(20) path 'ZipCode',
    timezone varchar2(50) path 'TimeZone')
    ) loop
      l_geolocation('country'):=c.country;
      l_geolocation('city'):=c.city;
    end loop;
  end loop;
exception--The above request ends in an exception once it finishes reading all the infromation
  when utl_http.end_of_body then--So when it occurs, just returning the obtained information
    utl_http.end_response(l_http_response);
    return l_geolocation(p_division);
end xxen_geolocation;

Using the geolocation function

An example of the new function usage is FND User Login History with Geolocation Blitz report. Feel free to run it to see how it works. I do it just for fun sometimes because it’s very interesting to see where our customers login from.

Unfortunately xxen_geolocation function and FND User Login History with Geolocation can’t be used directly in your environment as you need to setup your own database wallet.
But you can use this post and code as a template for your own function and request. And I’m ready to help you, just let me know in the comments if you have any issues.