Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: modelling IPv6 as a number

Re: modelling IPv6 as a number

From: DA Morgan <>
Date: Wed, 20 Dec 2006 14:07:33 -0800
Message-ID: <> wrote:
> Sorry to be so slow to respond, I've been distracted by another project
> lately.
> My reasons for storing the IPs as numbers is for searching purposes. I
> have a list of ip addresses (numeric) and subnets (start and end IPs in
> numeric) it's now easy for me to search (where '1234' between startIP
> and endIP) or even perform a join. Aside from uniqueness issues
> associated with NAT, this works great for IPv4. But IPv6 creates a new
> problem.
> My only solution so far is to create an extra column for the left most
> digit but now every query get more cumbersome because 2 columns are
> involved and if I'm ranging across two or more left most digits...the
> query becomes a pain that is more error prone.
> I've heard that "Oracle Spatial" allows you to create your own data
> types. Does anybody know if that's true and if I can use it to create
> the equivalent of a number(39)?
> Thanks
> Dana
> On Dec 12, 8:28 pm, Andy Hassall <> wrote:

>> On Wed, 13 Dec 2006 10:51:52 +1100, Geoff Muldoon
>> <> wrote:
>>> says...
>>>> <> wrote:
>>>>> says...
>>>>>> If I'm not mistaken,IPv6requires 39 significant digits to represent
>>>>>> each possible IP as an integer. Oracle's maximum is  number(38). Has
>>>>>> anyone devised a scheme to store the integer value ofIPv6in oracle?
>>>>> Why ever would you store it as a number?  It isn't an attribute to which
>>>>> you can naturally apply *numeric* functions, adding twoIPv6addresses
>>>>> together doesn't make much sense.  Store it as a string.
>>>>  One situation where the fact that an IPv4 address is a number shows up is in
>>>> netmasks, for working out subnets and broadcast addresses. Presumably there are
>>>> some similar operations that still apply toIPv6addresses?
>>> subnet ~ substring Well, only for "simple" subnets like
>>> My general rule: if you add two values together and the result is
>>> meaningless, it's not a number, it's a string.
>>> Using that rule I win most arguments about also storing values such as
>>> phone numbers and postal/zip codes as strings instead of numbers. No argument at all about those.
>>  But with IP addresses you can also perhaps argue in a similar way to storing
>> dates; a number _is_ the native form for IP addresses (e.g. in_addr in C), the
>> string value is the human-readable formatted value. You don't store dates as
>> formatted strings, you store them using the native date format.
>>  And whilst you can't meaningfully add two IPv4 addresses together, you can add
>> 1 to get the adjacent address, and do a limited number of other numeric
>> operations on it (e.g. netmasks again).
>>  (But looking back at the thread it seems I've basically just said what Maxim
>> already posted, oh well I've typed it now :p)
>>  So storage as a varchar2 is probably fine unless the OP really needs these
>> operations. The C struct in6_addr presents itself as 16 unsigned 8-bit values,
>> so the OP could even go for 16 number(3) columns in a table; if he's working in
>> C it might make it easier to load and save, although it'd be a weird table and
>> probably no help for languages other than C!
>> --
>> Andy Hassall :: :: disk and FTP usage analysis tool

All currently supported versions of Oracle allow you to create your own data types: You don't need Spatial.

Daniel A. Morgan
University of Washington
(replace x with u to respond)
Puget Sound Oracle Users Group
Received on Wed Dec 20 2006 - 16:07:33 CST

Original text of this message