IP Address arithmetics in Oracle?

From: Rodney Volz <rodney_at_watchtower.LF.net>
Date: 2 Mar 1999 20:11:31 GMT
Message-ID: <7bhglj$ma0$1@news.LF.net>

Hello,

I have a table 'ipacc' looking like this:

 Name                            Null?    Type
 ------------------------------- -------- ----
 CHUNK                                    NUMBER(32)
 SRC                                      RAW(4)
 DST                                      RAW(4)
 PACKETS                                  NUMBER(32)
 BYTES                                    NUMBER(32)

Each row represents an amount of data (PACKETS, BYTES) that was transmitted from ip address 'SRC' to address 'DST'.

I'd like to be able to SELECT for all IP addresses that belong to a given subnet. An ip address "belongs to a subnet" if the following expression is true:

ipadr & subnet_mask == subnet_number

For example:

192.168.1.1 & 255.255.255.0 == 192.168.1.0

A select statement like this one would be fine:

SELECT * FROM ipacc WHERE src & netmask = network-number;

This of course won't work since oracle doesn't seem to know about binary AND/OR/NOT constructs.

Any way to accomplish this? What would be the closest possible solution? What it boils down to is basically the question for binary arithmetic in Oracle.

Regards,
-Rodney Volz Received on Tue Mar 02 1999 - 14:11:31 CST