MaxMind GeoIP Support for Oracle Databases written by Sascha 'SieGeL' Pfalz
-----------------------------------------------------------------------------

1. What this is
~~~~~~~~~~~~~~~
This is a small set of functions + table definition to load the MaxMind
GeoIP Country Database into Oracle (see http://www.maxmind.com for further
informations). It should be easy to setup and allows you to query the GeoIP
database with a set of functions. I've tested this successfully with 
Oracle 8i and 9i Databases, earlier version might work, but I'm not sure.


2. How To install
~~~~~~~~~~~~~~~~~
I've provided both the table definition and a small PL/SQL package to use
the table, both in separate textfiles, which should be executable directly
from SQL*Plus. So follow these steps to install GeoIP support:

1. Open a command shell and change to the directory where both *.sql files
   are located.
   
2. Login to SQL*Plus under the user you wish to use the GeoIP functionality
   and enter the following:
   
   @GeoIP_table
   
   You should see some output stating that Table, comment and index where
   created successfully. No errors should occur, else please check the 
   corresponding file GeoIP_table.sql for any errors. 
   At least here it works :)
   
   Next you have to install the PL/SQL code (if you wish of course, you may
   also use everything WITHOUT it, it's just supplied for easier usage).
   
   @GeoIP_pack
   
   This should install both package header and body.
   
3. Finally we have to fill our table. Go to MaxMind Website and download the
   free country database in CSV format. Use the following URL for direct dl:
   
   http://www.maxmind.com/download/geoip/database/GeoIPCountryCSV.zip
   
   Unzip the file in the folder where the file GeoIP_import.ctl is located,
   so that we can import the data with SQL*Loader. So you should have now
   both the GeoIPCountryWhois.csv and the GeoIP_import.ctl file together
   in the same directory. Now enter:
   
   sqlldr USERID=<ORACLE_USER_CONNECT> CONTROL=GeoIP_import.ctl
   
   After some time (depends on how fast your machine is :) SQL*Loader should
   be finished without errors and should have written ~66768 Records. You may
   check the file GeoIP_import.log for any errors, but normally this should be
   working fine. 
   Note: The control file I've supplied imports in REPLACE mode, which means
   that the table GEOIP will be truncated BEFORE importing the file! Please
   keep this in mind in case you have altered the table yourself.
   
Thats it for installation, you have now a simple and easy way to query any
ip address for the country code or country name thanks to MaxMind :)


3. Usage
~~~~~~~~
I've provided a small PL/SQL package called UTL_GEOIP to make queries against
the GeoIP table a little bit more comfortable. So to query which country is
behind the random choosen ip address 82.183.132.154 you may enter:

SELECT UTL_GEOIP.GETCOUNTRYNAME('82.183.132.154') FROM DUAL;

Which results in 'Sweden' here. You may also query for the country code:

SELECT UTL_GEOIP.GETCC('82.183.132.154') FROM DUAL;

which returns 'SE' here.

Please refer to the file GeoIP_pack.sql to see how this all goes, or take
a look to the maxmind.com Website, which provides very good informations
how to use the GeoIP database.


4. Final Words
~~~~~~~~~~~~~~
I have written these scripts mainly to have the ip-to-country informations
available at work, but I think others may benifit from these functions, too.
If you have any suggestions, bug-reports or other comments about this package,
feel free to drop me an email (see bottom of text for addresses).

A big THANKS must go to maxmind.com for providing their excellent database 
under the GPL, and therefor all of my additional small scripts are also 
licenced under the GPL of course. 


5. Contact
~~~~~~~~~~
Here are some sources how to reach me:

E-Mail: Sascha.Pfalz@t-online.de
   WWW: http://www.saschapfalz.de
   ICQ: 9691810 (not very often in use however)

Have fun with these little scripts!

Regards,

Sascha 'SieGeL' Pfalz
-----------------------------------------------------------------------[EOF]---