Getting Client Location Information in Oracle Database 23ai PL/SQL

Step 1: Capture Client IP Address

When working with web applications, the client IP is often passed through proxies or load balancers. Use the X-FORWARDED-FOR header to get the original client IP:

SELECT
SUBSTR(owa_util.get_cgi_env('X-FORWARDED-FOR'), 1, 
   INSTR(owa_util.get_cgi_env('X-FORWARDED-FOR') || ',', ',') - 1) AS client_ip
FROM
dual;

Step 2: Configure Network Access Control

Before making external HTTP requests, you need to grant your database user permission to connect to external hosts:

BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '*',
ace  => xs$ace_type(
          privilege_list => xs$name_list('connect'),
          principal_name => 'YOUR_USERNAME',
          principal_type => xs_acl.ptype_db
       )
);
END;
/

Important notes:

• Replace 'YOUR_USERNAME' with your actual database username.
• Using '*' as the host grants access to all external hosts; consider restricting this to specific domains like ipapi.co for better security.
• You need appropriate privileges to execute DBMS_NETWORK_ACL_ADMIN procedures.

Step 3: Query Geolocation API

Use UTL_HTTP to call a geolocation service and retrieve location data:

DECLARE
l_http_request   UTL_HTTP.req;
l_http_response  UTL_HTTP.resp;
l_response_text  CLOB;
l_line           VARCHAR2(32767);
l_ip             VARCHAR2(100);
BEGIN
-- Get client IP address
l_ip := SYS_CONTEXT('USERENV', 'IP_ADDRESS');

-- Alternative: Get from X-FORWARDED-FOR
-- l_ip := SUBSTR(owa_util.get_cgi_env('X-FORWARDED-FOR'), 1, 
--                INSTR(owa_util.get_cgi_env('X-FORWARDED-FOR') || ',', ',') - 1);

-- Call GeoIP API
l_http_request := UTL_HTTP.begin_request(
'https://ipapi.co/' || l_ip || '/json/'
);

l_http_response := UTL_HTTP.get_response(l_http_request);

-- Read response
LOOP
UTL_HTTP.read_line(l_http_response, l_line, TRUE);
l_response_text := l_response_text || l_line;
END LOOP;

UTL_HTTP.end_response(l_http_response);

-- Display the result
DBMS_OUTPUT.PUT_LINE('Location Data: ' || l_response_text);

EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(l_http_response);
DBMS_OUTPUT.PUT_LINE('Location Data: ' || l_response_text);
WHEN OTHERS THEN
IF l_http_response.status_code IS NOT NULL THEN
    UTL_HTTP.end_response(l_http_response);
END IF;
RAISE;
END;
/

Step 4: Parse JSON Response

The API returns a JSON response that you can parse using Oracle's JSON functions:

Example API Response:

{
"ip": "102.23.54.10",
"city": "Dubai",
"region": "Dubai",
"country_name": "United Arab Emirates",
"latitude": 25.276987,
"longitude": 55.296249,
"timezone": "Asia/Dubai"
}

Parse the JSON data:

SELECT
json_value(:json_data, '$.ip') AS ip,
json_value(:json_data, '$.city') AS city,
json_value(:json_data, '$.region') AS region,
json_value(:json_data, '$.country_name') AS country,
json_value(:json_data, '$.latitude') AS latitude,
json_value(:json_data, '$.longitude') AS longitude,
json_value(:json_data, '$.timezone') AS timezone
FROM dual;

Complete Working Example

Here's a complete procedure that combines all steps:

CREATE OR REPLACE PROCEDURE get_client_location(
p_ip IN VARCHAR2 DEFAULT NULL
) AS
l_http_request   UTL_HTTP.req;
l_http_response  UTL_HTTP.resp;
l_response_text  CLOB;
l_line           VARCHAR2(32767);
l_ip             VARCHAR2(100);
BEGIN
-- Determine IP address
IF p_ip IS NOT NULL THEN
l_ip := p_ip;
ELSE
-- Try X-FORWARDED-FOR first
l_ip := SUBSTR(owa_util.get_cgi_env('X-FORWARDED-FOR'), 1, 
               INSTR(owa_util.get_cgi_env('X-FORWARDED-FOR') || ',', ',') - 1);

-- Fallback to direct IP
IF l_ip IS NULL THEN
    l_ip := SYS_CONTEXT('USERENV', 'IP_ADDRESS');
END IF;
END IF;

DBMS_OUTPUT.PUT_LINE('Querying location for IP: ' || l_ip);

-- Call GeoIP API
l_http_request := UTL_HTTP.begin_request(
'https://ipapi.co/' || l_ip || '/json/'
);

l_http_response := UTL_HTTP.get_response(l_http_request);

-- Read response
LOOP
UTL_HTTP.read_line(l_http_response, l_line, TRUE);
l_response_text := l_response_text || l_line;
END LOOP;

EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(l_http_response);

-- Parse and display results
DBMS_OUTPUT.PUT_LINE('IP: ' || json_value(l_response_text, '$.ip'));
DBMS_OUTPUT.PUT_LINE('City: ' || json_value(l_response_text, '$.city'));
DBMS_OUTPUT.PUT_LINE('Region: ' || json_value(l_response_text, '$.region'));
DBMS_OUTPUT.PUT_LINE('Country: ' || json_value(l_response_text, '$.country_name'));
DBMS_OUTPUT.PUT_LINE('Timezone: ' || json_value(l_response_text, '$.timezone'));

WHEN OTHERS THEN
IF l_http_response.status_code IS NOT NULL THEN
    UTL_HTTP.end_response(l_http_response);
END IF;
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
RAISE;
END;
/

Usage

Enable server output and execute the procedure:

SET SERVEROUTPUT ON;
EXEC get_client_location();

Or test with a specific IP:

EXEC get_client_location('8.8.8.8');

Important Considerations

• Security: Using '*' in ACL grants access to all hosts. Restrict to specific domains in production environments.
• API Limits: Free geolocation APIs often have rate limits. Consider using paid services or implementing caching for production use.
• IPv6 Support: The code handles both IPv4 and IPv6 addresses. The X-FORWARDED-FOR parsing works for both formats.
• Error Handling: Always implement proper exception handling when making external HTTP calls, as network issues can occur.
• Oracle Wallet: For HTTPS connections, ensure your Oracle Wallet is properly configured with trusted certificates.