Representing IPv4/IPv6 addresses in Oracle

Question

In Oracle, what is the appropriate data type or technique for representing network addresses, which addresses may be IPv4 or IPv6?

Background: I'm converting a table recording network activity, built using the PostgreSQL inet data type to hold both v4 and v6 addresses in the same table.

No row contains both v4 and v6 addresses, however. (That is, a record is either from a machine's v4 stack, or a machine's v6 stack.)

Answer 1

In Oracle, what is the appropriate data type or technique for representing network addresses, which addresses may be IPv4 or IPv6

There are two approaches :

1. storing only.
2. storing the conventional representation

For storing only. An IPV4 address should be an integer (32bits are enough). For IP V6, 128 bits, INTEGER (which is similar to Number(38)) will do. Of course, that's storing. That approach takes the view that the representation is a matter for the application.

If one take the opposite strategy, of storing the conventional representation, one needs to make sure that IP V4 and IPV6 addresses have only one conventional (string) representation. It's well-known for ipV4. As for IPV6, there is also a standard format.

My preference goes to the first strategy. In the worst case, you can adopt an hybrid approach (non acid though) and store both the binary and the ascii representation side by side with "priority" to the binary value.

No row contains both v4 and v6 addresses, however.

The standard representation of a IPV4 address in IPV6 format is : ::ffff:192.0.2.128.

I don't know the context but I would however reserve 2 columns, one for IPV4 and the other for a distinct ipV6 address.

Update
Following a good comment by @sleepyMonad's, I'd like to point out that instead of the Number data type it is preferable to use the INTEGER data type, which will happily accommodate the highest possible value that can be expressed with a 128 bits integer 'ff...ff' (which would need 39 decimal digits). 38 is the highest power of ten ranging from 0 to 9 that can be encoded on 128 bits but one can still insert the maximum unsigned value for 2**128 - 1 (decimal 340282366920938463463374607431768211455). Here is a small test to illustrate this possibility.

create table test (
  id integer primary key,
  ipv6_address_bin INTEGER );

-- Let's enter 2**128 - 1 in the nueric field
insert into test (id, ipv6_address_bin) values ( 1, to_number ( 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF', 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') ) ;

-- retrieve it to make sure it's not "truncated".
select to_char ( ipv6_address_bin, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' ) from test where id = 1 ;
-- yields 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'

select to_char ( ipv6_address_bin ) from test where id = 1 ;
-- yields 340282366920938463463374607431768211455

select LOG(2, ipv6_address_bin) from test where id = 1 ;
-- yields 128

select LOG(10, ipv6_address_bin) from test where id = 1 ;
-- yields > 38