Get GEOLOCATION based on the IP address of the user based on NEUSTAR data feed in PL/SQL

 CREATE OR REPLACE PROCEDURE GET_GEOLOCATION(

    p_ip_address   IN  VARCHAR2,

    p_city         OUT VARCHAR2,

    p_state        OUT VARCHAR2,

    p_country      OUT VARCHAR2,

    p_latitude     OUT NUMBER,

    p_longitude    OUT NUMBER,

    p_status_code  OUT NUMBER

) AS

    v_decimal_ip   NUMBER;

BEGIN

    -- Validate and convert IPv4 to decimal

    IF REGEXP_LIKE(p_ip_address, '^((25[0-5]|2[0-4][0-9]|[0-1]?[0-9][0-9]?)\.){3}(25[0-5]|2[0-4][0-9]|[0-1]?[0-9][0-9]?)$') THEN

        v_decimal_ip := (TO_NUMBER(REGEXP_SUBSTR(p_ip_address, '^[^.]+')) * POWER(256, 3)) +

                        (TO_NUMBER(REGEXP_SUBSTR(p_ip_address, '[^.]+', 1, 2)) * POWER(256, 2)) +

                        (TO_NUMBER(REGEXP_SUBSTR(p_ip_address, '[^.]+', 1, 3)) * POWER(256, 1)) +

                        (TO_NUMBER(REGEXP_SUBSTR(p_ip_address, '[^.]+', 1, 4)));

    

    -- Validate and convert IPv6 to decimal

    ELSIF REGEXP_LIKE(p_ip_address, '^([0-9a-fA-F]{1,4}:){7}[0-9a-fA-F]{1,4}$') THEN

        -- IPv6 is usually stored as binary or separate parts, but this assumes a precomputed decimal representation

        v_decimal_ip := TO_NUMBER(UPPER(TRANSLATE(p_ip_address, ':', '')), 'XXXXXXXXXXXXXXXX');

    ELSE

        p_status_code := 400; -- Invalid IP address

        RETURN;

    END IF;

    -- Lookup IP in GEOLOCATION table

    BEGIN

        SELECT city, state, country, latitude, longitude

        INTO p_city, p_state, p_country, p_latitude, p_longitude

        FROM GEOLOCATION

        WHERE v_decimal_ip BETWEEN FROM_IP_ADDR AND TO_IP_ADDR;

        

        p_status_code := 200; -- Success

    EXCEPTION

        WHEN NO_DATA_FOUND THEN

            p_status_code := 404; -- IP address not found

    END;

END GET_GEOLOCATION;