maxwell.dana_at_gmail.com 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 <a..._at_andyh.co.uk> wrote:
>> On Wed, 13 Dec 2006 10:51:52 +1100, Geoff Muldoon
>>
>>
>>
>> <geoff.muld..._at_trap.gmail.com> wrote:
>>> a..._at_andyh.co.uk says...
>>>> <geoff.muld..._at_trap.gmail.com> wrote:
>>>>> maxwell.d..._at_gmail.com 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 255.255.255.0.
>>> 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 :: a...@andyh.co.uk ::http://www.andyh.co.ukhttp://www.andyhsoftware.co.uk/space:: 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
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Dec 20 2006 - 16:07:33 CST
